首先参考 ubuntu 16.04配置MySQL主从同步 实现同步,接下来执行如下操作
主数据库master配置
#生成加密证书，默认在/var/lib/mysql/下生成ca-key.pem，server-key.pem，client-key.pem $ sudo mysql_ssl_rsa_setup --uid=mysql #有时候需要手工修改一下证书所有者 $ sudo chown mysql:mysql /var/lib/mysql/*.pem $ sudo ls -l /var/lib/mysql/ | grep .pem -rw------- 1 mysql mysql 1679 Apr 2 22:53 ca-key.pem #CA私钥 -rw-r--r-- 1 mysql mysql 1107 Apr 2 22:53 ca.pem #自签的CA证书，客户端连接也需要提供 -rw-r--r-- 1 mysql mysql 1107 Apr 2 22:53 client-cert.pem #客户端连接服务器端需要提供的证书文件 -rw------- 1 mysql mysql 1679 Apr 2 22:53 client-key.pem #客户端连接服务器端需要提供的私钥文件 -rw------- 1 mysql mysql 1675 Apr 2 22:53 private_key.pem #私钥/公钥对的私有成员 -rw-r--r-- 1 mysql mysql 451 Apr 2 22:53 public_key.pem #私钥/公钥对的共有成员 -rw-r--r-- 1 mysql mysql 1107 Apr 2 22:53 server-cert.pem #服务器端证书文件 -rw------- 1 mysql mysql 1679 Apr 2 22:53 server-key.pem #服务器端私钥文件 #配置服务器 $ sudo cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf.bak $ sudo sed -i "s/# ssl-ca=\/etc\/mysql\/cacert.pem/ssl-ca=\/var\/lib\/mysql\/ca.pem/g" /etc/mysql/mysql.conf.d/mysqld.cnf $ sudo sed -i "s/# ssl-cert=\/etc\/mysql\/server-cert.pem/ssl-cert=\/var\/lib\/mysql\/server-cert.pem/g" /etc/mysql/mysql.conf.d/mysqld.cnf $ sudo sed -i "s/# ssl-key=\/etc\/mysql\/server-key.pem/ssl-key=\/var\/lib\/mysql\/server-key.pem/g" /etc/mysql/mysql.conf.d/mysqld.cnf #重启MySQL服务 $ sudo service mysql restart #查看服务器状态，是否已经启用SSL $ mysql -u root -p -e "show global variables like '%ssl%'" Enter password: +---------------+--------------------------------+ | Variable_name | Value | +---------------+--------------------------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | /var/lib/mysql/ca.pem | | ssl_capath | | | ssl_cert | /var/lib/mysql/server-cert.pem | | ssl_cipher | | | ssl_crl | | | ssl_crlpath | | | ssl_key | /var/lib/mysql/server-key.pem | +---------------+--------------------------------+ #修改已存在用户,要求必须通过SSL才能同步,完成主从同步之后，从库可能会无法正常同步这个修改，需要手工跳过一个错误 "stop slave;SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;start slave;" $ mysql -u root -p -e "ALTER USER 'repl'@'182.254.149.39' REQUIRE SSL;" # 阻止数据库记录写入，避免后期我们备份数据库的时候数据发生变动 # 该命令对于普通账号的只读模式，root 账号无效，因此访问数据库的账号 # 尽量不要使用root账号，如果是root 账号，只能暂时停止所有访问数据库的服务了 $ mysql -u root -p -e "set global read_only=1;" #查询并记录主库的同步位置 $ mysql -u root -p -e "SHOW MASTER STATUS;" Enter password: +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000021 | 12144639 | | | | +------------------+----------+--------------+------------------+-------------------+ # 执行 ubuntu 16.04配置MySQL主从同步 http://www.mobibrw.com/?p=10541 里的备份脚本 $ sudo bash backup_wordpress.sh # 取消普通账号的只读模式 $ mysql -u root -p -e "set global read_only=0;"
从服务器slave配置
$ sudo mkdir /etc/mysql/ssl
#从服务端获取证书
$ sudo scp -P 22 -r root@www.mobibrw.com:/var/lib/mysql/ca.pem /etc/mysql/ssl/
$ sudo chown mysql:mysql /etc/mysql/ssl/ca.pem
$ sudo scp -P 22 -r root@www.mobibrw.com:/var/lib/mysql/client-cert.pem /etc/mysql/ssl/
$ sudo chown mysql:mysql /etc/mysql/ssl/client-cert.pem
$ sudo scp -P 22 -r root@www.mobibrw.com:/var/lib/mysql/client-key.pem /etc/mysql/ssl/
$ sudo chown mysql:mysql /etc/mysql/ssl/client-key.pem
$ mysql -u root -p -e "stop slave;"
#获取最后同步的位置，为后续恢复进行准备
$ mysql -u root -p -e "show slave status\G;" | grep Exec_Master_Log_Pos:
Enter password:
Exec_Master_Log_Pos: 12178842
$ mysql -u root -p -e "show slave status\G;" | grep Master_Log_File
Enter password:
Master_Log_File: mysql-bin.000021
Relay_Master_Log_File: mysql-bin.000021
#修改同步信息
$ mysql -u root -p -e "CHANGE MASTER TO MASTER_HOST='www.mobibrw.com', MASTER_USER='repl', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysql-bin.000021',MASTER_LOG_POS=12178842 ,master_ssl=1,master_ssl_ca='/etc/mysql/ssl/ca.pem', master_ssl_capath='/etc/mysql/ssl', master_ssl_cert='/etc/mysql/ssl/client-cert.pem', master_ssl_key='/etc/mysql/ssl/client-key.pem';"
$ mysql -u root -p -e "start slave;"
#查看从库状态
$ mysql -u root -p -e "show slave status\G;"
Enter password:
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: www.mobibrw.com
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000021
Read_Master_Log_Pos: 12701244
Relay_Log_File: VM-xxx-xxx-xxxxx-relay-bin.000003
Relay_Log_Pos: 195033
Relay_Master_Log_File: mysql-bin.000021
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: 12701244
Relay_Log_Space: 523107
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /etc/mysql/ssl/ca.pem
Master_SSL_CA_Path: /etc/mysql/ssl
Master_SSL_Cert: /etc/mysql/ssl/client-cert.pem
Master_SSL_Cipher:
Master_SSL_Key: /etc/mysql/ssl/client-key.pem
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: 1
Master_UUID: xxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxx
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:
注意，偶尔在系统升级的时候，从库可能会丢失同步状态配置。这时候，我们需要重新同步，此时我们从从设备`/var/lib/mysql/master.info`中找到被中断的同步点。
里面的内容一般如下：
25 mysql-bin.000582 4765083 www.mobibrw.com xxxx xxxx 3306 60 1 /etc/mysql/ssl/ca.pem /etc/mysql/ssl /etc/mysql/ssl/client-cert.pem /etc/mysql/ssl/client-key.pem 0 30.000 0 0b674082-f01d-11e9-8f8c-00163e0a4ffe 86400 0
注意`mysql-bin.000582`下面的`4765083`就是同步位置，在恢复的时候，就是这两个关键数据。