mysql主备搭建

mysql主备(master-slave)搭建

环境：mysql5.7.21(master),mysql5.7.20(slave)

===========================master server===================

mysql> select User,Host from user;
+---------------+-----------+
| User          | Host      |
+---------------+-----------+
| rootpp        | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)

//创建复制账号
mysql> grant replication slave on *.* to repl_user identified by '111111';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select Host,User from user;
+-----------+---------------+
| Host      | User          |
+-----------+---------------+
| %         | repl_user     |
| %         | rootpp        |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+

vi mysqld.cnf
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
log-bin         = master-bin      //二进制日志
log-bin-index   = master-bin.index   //二进制日志文件索引
server-id       = 1
# By default we only accept connections from localhost
# bind-address  = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0


==============================slave server==========================
vi mysqld.cnf
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
server-id       = 2
relay-log-index = slave-relay-bin.index  //中继日志文件索引
relay-log       = slave-relay-bin   //中继日志
# By default we only accept connections from localhost
# bind-address  = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

slave_mysql>change master to \
     master_host = '192.168.1.44',\ //内网还是外网
     master_port = 3306,\ //number
     master_user = 'repl_user',\
     master_password = '111111';

slave_mysql> start slave;


mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 168.*.*.36
                  Master_User: replicator
                  Master_Port: 32091
                Connect_Retry: 60
              Master_Log_File: master-bin.000002
          Read_Master_Log_Pos: 2251
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 1444
        Relay_Master_Log_File: master-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: replica

指定bin-log开始同步
mysq> change master to\
        master_host = '168.*.*.36',\
        master_port = 3306,\
        master_user = 'replicator',\
        master_password = '******',\
        master_log_file='master-bin.000002',\
        master_log_pos=2251; //--> Read_Master_Log_Pos

slave命令

# 开启从库同步
mysql> start slave;

# 关闭从库同步
mysql> stop slave;

# 查看同步状态
mysql> show slave status\G;