首先参考 ubuntu 16.04配置MySQL主从同步 实现同步,接下来执行如下操作
主数据库master配置
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
#生成加密证书，默认在/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配置
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 |
$ 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中找到被中断的同步点。
里面的内容一般如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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就是同步位置，在恢复的时候，就是这两个关键数据。