mysql集群配置，主从复制，一主一备。

环境

主库：mysqld  Ver 5.7.26 for Linux on x86_64 (MySQL Community Server (GPL))       Red Hat 4.8.5-11

从库：mysqld  Ver 5.7.26-0ubuntu0.16.04.1 for Linux on x86_64 ((Ubuntu))

原理

场景以及问题 

权限

1.最好两台数据库都搞成这个样子的权限

mysql -u root -p
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION
flush  privileges ;
show grants for root;
update mysql.user set authentication_string = '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' where user='root';
exit;
service mysql restart; 

主库配置及其操作

1.主库配置

server-id               = 10
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size   = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name
#
# * InnoDB
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
innodb_flush_log_at_trx_commit=1
sync_binlog=1

2.重启，This procedure uses FLUSH TABLES WITH READ LOCK, which blocks COMMIT operations forInnoDB tables.

mysql> FLUSH TABLES WITH READ LOCK;

3.记录二进制文件位置

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

4.主库导出数据

mysqldump -uroot -p --single-transaction --all-databases --master-data > dbdump.db

5. 解锁事务锁，On the master, released the read lock:

mysql> UNLOCK TABLES;

从库配置及其操作

1.配置

server-id               = 11

2.倒入主库转储db文件

mysql -u root -p < dbdump.db 

 3.指向主库，其文件名写错了以后mysql-bin.000002，照样能复制

CHANGE MASTER TO
    -> MASTER_HOST='192.168.43.114',
    -> MASTER_USER='root',
    -> MASTER_PASSWORD='123456',
    -> MASTER_LOG_FILE='mysql-bin.000002',
    -> MASTER_LOG_POS=154;

4.启动从库复制线程

start slave;

5.测试成功

mysql> show slave status\G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.43.114
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 3765
               Relay_Log_File: bogon-relay-bin.000003
                Relay_Log_Pos: 3978
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes