MySQL 8.0主备实践 – Ubuntu 1910

本文为MySQL一主一备复制配置示例。假设前提如下:

  • 主库ip为10.0.0.1
  • 备库ip为10.0.0.2,
  • 需要复制的数据库为test_db,
  • 复制用户为replication,密码为123456。
  • master server-id 为1,slave server-id 为102。

1、Mysql安装并开启SSL

apt install mysql-server
sudo mysql_ssl_rsa_setup --uid=mysql   
show variables  like '%ssl%';

2、主库配置

创建用户:

CREATE USER 'replication'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE,replication ON *.* TO 'replication'@'%' require ssl;

ALTER USER 'replication'@'%' REQUIRE SSL;

flush privileges;

/etc/mysql/mysql.conf.d/mysqld.cnf 配置文件修改示例:

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_expire_logs_seconds      = 2592000
max_binlog_size   = 100M
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
binlog_do_db            = test_db

3、数据库复制

主库锁表:

flush table with read lock;
show master status \G;
* 1. row *
              File: mysql-bin.000001
          Position: 154
      Binlog_Do_DB: test_db
  Binlog_Ignore_DB: 
 Executed_Gtid_Set: 
 1 row in set (0.01 sec)
 ERROR: 
 No query specified

DUMP数据

mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -u root -p --databases test_db > test_db_$(date +\%Y-\%m-\%d)_dump.sql

主库解锁:

unlock tables;

4、备库配置

创建用户:

CREATE USER 'lijun'@'%' IDENTIFIED BY '123456';
GRANT ALL ON `test_%`.* TO 'lijun'@'%';

导入主库相关信息:

CHANGE MASTER TO MASTER_HOST='10.0.0.1', MASTER_USER='replication', MASTER_PASSWORD='123456', master_ssl=1, MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;

MASTER_LOG_FILE,MASTER_LOG_POS需要替换为主库的实际值,具体查看方法:

show master status \G;

配置示例:

server-id = 102
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
read-only = 1
relay-log = /var/log/mysql/relay-bin
log-slave-updates = 1
slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 16
master_info_repository = TABLE
relay_log_info_repository= TABLE
relay_log_recovery = ON
slave_preserve_commit_order=0

导入数据

mysql 命令导入

mysql -ulijun -p123456 < test_db01-11-2019_dump.sql

source 命令导入

create database test_db;
source /home/lijun/test_db01-11-2019_dump.sql;

启动备库

reset slave;
start slave;

show slave status \G;
* 1. row *
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 10.0.0.1
                   Master_User: replication
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000001
           Read_Master_Log_Pos: 154
                Relay_Log_File: relay-bin.000228
                 Relay_Log_Pos: 154
         Relay_Master_Log_File: mysql-bin.000001
              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: 154
               Relay_Log_Space: 154
               Until_Condition: None
                Until_Log_File: 
                 Until_Log_Pos: 0
            Master_SSL_Allowed: Yes
            Master_SSL_CA_File: 
            Master_SSL_CA_Path: 
               Master_SSL_Cert: 
             Master_SSL_Cipher: 
                Master_SSL_Key: 
         Seconds_Behind_Master: 1
 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: 1
                   Master_UUID: 5b5494c1-a789-11e9-8061-f23c91aeb5d5
              Master_Info_File: mysql.slave_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.02 sec)
 ERROR: 
 No query specified

5、常见问题

Waiting for dependent transaction to commit

解决方案:

检查主库是否有锁或者rt比较高的SQL。

6、参考文章

https://plusbryan.com/mysql-replication-without-downtime

留下评论