MySQL 主从从安装配置

taotie 3月前 ⋅ 84 阅读

准备工作

为MySQL自定义网络

[root@centos] docker network create --driver bridge --subnet 28.44.0.0/16 --gateway 28.44.0.1 mynet

准备MySQL配置文件

主服务器 28.44.9.80

vi /home/taotie/volumes/mysql/dbnode/my.cnf

[client]
port = 3306
default-character-set = utf8mb4

[mysql]
port = 3306
default-character-set = utf8mb4

[mysqld]
##########################
# log bin
##########################
server-id = 2844                #必须唯一
log_bin = mysql-bin             #开启及设置二进制日志文件名称
binlog_format = MIXED
sync_binlog = 1
expire_logs_days =7             #二进制日志自动删除/过期的天数。默认值为0,表示不自动删除。

binlog-do-db = db1          	#要同步的数据库
binlog-do-db = db2         		#要同步的数据库
binlog-do-db = db3    			#要同步的数据库

binlog-ignore-db = mysql        #不需要同步的数据库
binlog_ignore_db = information_schema
binlog_ignore_db = performation_schema
binlog_ignore_db = sys

##########################
# character set
##########################
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET collation_connection = utf8mb4_general_ci'
init_connect='SET NAMES utf8mb4'
skip-character-set-client-handshake

从服务器 28.44.9.81

vi /home/taotie/volumes/mysql/dbnode0/my.cnf

[client]
port = 3306
default-character-set = utf8mb4

[mysql]
port = 3306
default-character-set = utf8mb4

[mysqld]
##########################
# log bin
##########################
server-id = 28440

log_bin = mysql-bin             #开启及设置二进制日志文件名称
binlog_format = MIXED
sync_binlog = 1
expire_logs_days =7             #二进制日志自动删除/过期的天数。默认值为0,表示不自动删除。
log_slave_updates = 1           #【关键点】从主服务器接收到的更新同时要写入二进制日志

binlog-do-db = db1          	#要同步的数据库
binlog-do-db = db2         		#要同步的数据库
binlog-do-db = db3    			#要同步的数据库
binlog-ignore-db = mysql        #不需要同步的数据库
binlog_ignore_db = information_schema
binlog_ignore_db = performation_schema
binlog_ignore_db = sys

##########################
# character set
##########################
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET collation_connection = utf8mb4_general_ci'
init_connect='SET NAMES utf8mb4'
skip-character-set-client-handshake

从从服务器 28.44.9.82

vi /home/taotie/volumes/mysql/dbnode1/my.cnf

[client]
port = 3306
default-character-set = utf8mb4

[mysql]
port = 3306
default-character-set = utf8mb4

[mysqld]
server-id = 28441

##########################
# character set
##########################
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET collation_connection = utf8mb4_general_ci'
init_connect='SET NAMES utf8mb4'
skip-character-set-client-handshake

lower_case_table_names=1
innodb_file_per_table=1

以上三个文件分别为三台Mysql服务器的配置,先按照指定目录保存好。后续启动容器的时候会用到。

拉取安装mysql

[root@centos] # docker pull mysql:5.7.37

配置mysql

主库

启动容器

[root@centos] # docker run -itd -e MYSQL_ROOT_PASSWORD=123456  --net mynet --ip 28.44.9.80 --name dbnode -v /home/taotie/volumes/mysql/dbnode/my.cnf:/etc/mysql/my.cnf mysql:5.7.18

登陆容器后配置

[root@centos] # mysql -uroot -p123456 -h 28.44.9.80

创建同步账号

mysql> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
Query OK, 0 rows affected (0.00 sec)

检查服务器状态

查看log_bin是否开启
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set

查看master状态
mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000004
         Position: 609
     Binlog_Do_DB: db1,db2,db3
 Binlog_Ignore_DB: mysql,information_schema,performation_schema,sys
Executed_Gtid_Set: 
1 row in set (0.00 sec)

ERROR: 
No query specified

从库

###启动

[root@centos] # docker run -itd -e MYSQL_ROOT_PASSWORD=123456  --net mynet --ip 28.44.9.81 --name dbnode0 -v /home/taotie/volumes/mysql/2844mysql0/my.cnf:/etc/mysql/my.cnf mysql:5.7.18

登陆容器后配置

[root@centos] # mysql -uroot -p123456 -h 28.44.9.81

创建同步账号

mysql> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
Query OK, 0 rows affected (0.00 sec)

检查服务器状态

查看log_bin是否开启
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set

查看master状态
mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000004
         Position: 609
     Binlog_Do_DB: db1,db2,db3
 Binlog_Ignore_DB: mysql,information_schema,performation_schema,sys
Executed_Gtid_Set: 
1 row in set (0.00 sec)

ERROR: 
No query specified

绑定主库

mysql> stop slave;
Query OK, 0 rows affected

mysql> CHANGE MASTER TO 
MASTER_HOST='28.44.9.80',
MASTER_PORT=3306,
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=609;
Query OK, 0 rows affected

mysql> start slave;
Query OK, 0 rows affected

查看slave 状态

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 28.44.9.80
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 609
               Relay_Log_File: 4f20586b65bc-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 609
              Relay_Log_Space: 534
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2844
                  Master_UUID: e0dbead6-8b01-11ec-abf7-02421c2c0950
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

从从库

启动

[root@centos] # docker run -itd -e MYSQL_ROOT_PASSWORD=123456  --net mynet --ip 28.44.9.82 --name dbnode1 -v /home/taotie/volumes/mysql/2844mysql1/my.cnf:/etc/mysql/my.cnf mysql:5.7.18

登陆容器后配置

[root@centos] # mysql -uroot -p123456 -h 28.44.9.82

绑定从库

mysql> stop slave;
Query OK, 0 rows affected

mysql> CHANGE MASTER TO 
MASTER_HOST='28.44.9.81',
MASTER_PORT=3306,
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=609;
Query OK, 0 rows affected

mysql> start slave;
Query OK, 0 rows affected

查看slave 状态

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 28.44.9.81
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 609
               Relay_Log_File: 3d6ab42452f2-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 609
              Relay_Log_Space: 534
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 28440
                  Master_UUID: e6011684-8b01-11ec-ab5f-02421c2c0951
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

至此,主从从就配置完毕。

其它

处理主从不同步

跳过复制错误

mysql> stop slave;
mysql> set global sql_slave_skip_counter =1;
mysql> start slave;

需手动配合同步数据。

数据备份

mysqldump -uroot -h 28.44.9.80 -p'123456' -P3306 db3 > "/data/backup/db/db2.sql"

数据还原

mysql -uroot -h 28.44.9.80 -p'123456' db2<./db2.sql

全部评论: 0

    我有话说: