MySQL主从搭建

在Linux下搭建MySql的主从同步

实验平台：CentOS 7.4
MySQL版本:5.7.23

操作步骤：

• 一、主mysql
• 二、从mysql

一、主mysql

1.编辑master上mysql配置文件

$ vim /etc/my.cnf

log_bin=mysql-bin
server-id = 1

2.master mysql创建备份用户及授权

create user 'bak'@'%' identified by 'redhat';
grant select ,replication slave,replication client on *.* to 'bak'@'%' identified by 'redhat';
flush privileges;

二、从mysql

$ vim /etc/my.cnf

log_bin=mysql-bin
server_id = 2
relay_log=mysql-relay-bin
log_slave_updates=1
read_only=1

1.查看主信息

show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |    71193 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set

2.配置连接主库信息

CHANGE MASTER TO
MASTER_HOST='10.103.25.11', 
MASTER_USER='bak', 
MASTER_PASSWORD='redhat',
MASTER_LOG_FILE='mysql-bin.000001', 
MASTER_LOG_POS=71193;
或
change master to master_host='10.103.25.11',master_user='bak',master_password='redhat'; 

3.查看从信息

show slave status\G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.1.10.1
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000003
          Read_Master_Log_Pos: 326
               Relay_Log_File: relay-bin.000007
                Relay_Log_Pos: 611
        Relay_Master_Log_File: master-bin.000003
             Slave_IO_Running: Yes #确保I/O和SQL线程开启，即可实现数据同步
            Slave_SQL_Running: Yes  #确保I/O和SQL线程开启，即可实现数据同步
              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: 326
              Relay_Log_Space: 1184
              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
1 row in set (0.00 sec)

4.实战：主从不同步时，如何进行数据同步至一致

描述：当主服务器已经运行一段时间，并且存在不小的数据时，则需把主服务器备份，然后在从服务器恢复，从备份时所在的位置开始复制。

将主服务器上的数据做完全备份
$ mysqldump -u root -h 127.0.0.1 -p --lock-all-tables --all-databases --flush-logs --master-data=2 >/root/all.sql

Over~

798 0 2019-01-10

打赏一下，各位朋友请随意打赏！

×

如果觉得我的文章对你有用，请随意赞赏

取消