功能
归档
689
2019 年 01 月 10 日
MySQL主从搭建

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


操作步骤:


一、主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)
ERROR: No query specified

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

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

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

Over~

zhifubao_pay                weixin_pay

本站的发展离不开您的资助,金额随意,感谢来赏!


转载原创文章请注明,转载自: 林里克斯 » MySQL主从搭建
留言
Loading...

发表评论

电子邮件地址不会被公开。 必填项已用*标注