MySQL主从搭建

文章
林里克斯

Linux下搭建MySql的主从同步

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


操作步骤:


一、主mysql


1.编辑mastermysql配置文件

$ 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~

版权协议须知!

本篇文章来源于 Uambiguous ,如本文章侵犯到任何版权问题,请立即告知本站,本站将及时予与删除并致以最深的歉意

21 0 2019-01-10


分享:
使用 Nginx 代理做个下载站

类似许多镜像下载站和 ftp 一样,有时候需要去找一个包何...

阅读全文
CentOS7 开启 IPv6

随着 IPv4 的地址用尽,IPv6 的使用趋势愈来愈明显...

阅读全文
Linux 更新 gcc 版本

有些软件需要新版本的 gcc 环境支撑,CentOS 默认...

阅读全文
tar 解压报错 resolve failed

解压 tar 包报错 resolve failed 的解决...

阅读全文
icon_mrgreen.gificon_neutral.gificon_twisted.gificon_arrow.gificon_eek.gificon_smile.gificon_confused.gificon_cool.gificon_evil.gificon_biggrin.gificon_idea.gificon_redface.gificon_razz.gificon_rolleyes.gificon_wink.gificon_cry.gificon_surprised.gificon_lol.gificon_mad.gificon_sad.gificon_exclaim.gificon_question.gif
  • 默认
  • 护眼
  • 夜晚
  • 壁纸
  • 默认