mysql主从热备

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即可