本文为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_db3、数据库复制
主库锁表：
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.sqlsource 命令导入
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。