mysql主从热备
master 192.168.1.22
slave 192.168.1.23
参考
https://www.cnblogs.com/wcwnina/p/11336024.html
>>master配置
>修改master配置文件，在[mysqld]节点下修改
vi /etc/my.cnf
[mysqld]
server-id=1
log-bin=master-log-bin
innodb-file-per-table=ON
>重启master
service mysql restart
>在master上为slave建立一个连接帐户
create user 'replicate'@'192.168.1.23' identified by '123456';
为该账户授予REPLICATION SLAVE权限
grant replication slave on *.* to 'replicate'@'192.168.1.23';
刷新MySQL的系统权限相关表
flush privileges;
>测试在slave上用replicat帐户访问master数据库
mysql -h192.168.1.22 -ureplicate -p123456
>master锁表不让进新的数据，让slave定位同步位置，同步完成后，记得解锁。
flush tables with read lock;
解锁
unlock tables;
>查看master状态
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 519
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
注意File和Position参数，在slave配置主从关系时会用到
>>slave配置
>修改slave配置文件，在[mysqld]下修改
vi /etc/my.cnf
[mysqld]
log-bin=slave-log-bin
relay_log=relay-log
relay_log_index=relay-log.index
server-id=2
innodb_file_per_table=ON
>重启slave
mysql service mysql restart
>停止slave线程
stop slave;
>指定同步位置，其中 master_log_file, master_log_pos值分别是上面让注意的File和Position
change master to master_host='192.168.1.22',master_user='replicate',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=519;
>若同步成功，则可以开启slave线程
start slave;
>若报错失败，则停止slave线程、重置slave再重新同步
stop slave;
重置slave线程
reset slave;
stop slave;
重新同步
>查看slave状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.22
Master_User: replicate
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 519
Relay_Log_File: iZ8vb49726asyku7plxms6Z-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test
Replicate_Ignore_DB: mysql,information_schema,performance_schema
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: 519
Relay_Log_Space: 545
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: 1
Master_UUID: cd6f92dc-22ce-11ea-90bc-fa163e31e75e
Master_Info_File: /data/mysql/data/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.01 sec)
ERROR:
No query specified
mysql>
查看下面两项值均为Yes，即表示设置slave成功。
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
注意：mysql8.0以后的版本此处大概率会报错
Last_IO_Error: error connecting to master 'replicate@192.168.100.161:3306' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
由于mysql8.0的身份验证机制由原来的mysql_native_password
改为新的caching_sha2_password。
所以需要先在slave上连接 master 获取 public-key，如下：
mysql --ssl-mode=DISABLED -h 192.168.100.161 -uroot -p123456 --get-server-public-key
然后重新启动slave即可
网友评论