Mysql主从复制搭建（传统模式）

最新推荐文章于 2024-07-30 20:41:03 发布

王仲肖

最新推荐文章于 2024-07-30 20:41:03 发布

阅读量566

点赞数

分类专栏： MySQL 文章标签： mysql主从

本文链接：https://blog.csdn.net/wzx8731/article/details/85403082

版权

MySQL 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

mysql主从复制搭建（传统模式）

概述:

mysql 5.7.19

os : centos6.7

主库和从库分别搭建好数据库

1、主库配置

1.1 修改/etc/my.cnf配置文件

[mysqld]

log-bin = mysql-bin

server-id = 1 ####各节点必须唯一

binlog-do-db = testdb #####需要捕获binlog的数据库实例

###修改完成后，需重启数据库（如果已经开启binlog则不需要）

1.2 创建replicate用户

create user 'repl'@'%' identified by 'password';

grant replication slave on *.* to 'repl'@'%';

1.3 查看binlog状态

mysql> show master status\G

*************************** 1. row ***************************

File: mysql-bin.000006

Position: 765

Binlog_Do_DB: testdb

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

1.4 备份数据库

mysqldump -uroot --proot -A -B -R --events -x --master-data=2 -S /var/run/mysqld/mysqld.sock |gzip>/tmp/all.$(date +%F).sql.gz

-A：全备

-X：锁表

2、mysql从库配置

2.1 修改/etc/my.cnf

replicate-do-db = testdb

relay-log=relay-bin

relay-log-index=relay-bin.index

skip-slave-start=true ####slave进程不随mysql启动而启动

read_only=ON ###只读数据库，但超级用户仍旧读写的，只针对普通用户

2.2 还原数据库

gunzip all.2017-09-27.sql.gz

mysql -u root -proot </tmp/all.2017-09-27.sql

2.3 开启slave

查看,master_log_file,master_log_pos值

cat /tmp/all.2017-09-27.sql |grep "CHANGE MASTER TO" |head -1

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=154;

change master to master_host ='192.168.56.140',master_port=3306,master_user='repl',master_password='password',master_log_file='mysql-bin.000006',master_log_pos=154;

start slave; ####启动slave

2.4 查看slave状态

mysql> show slave status \G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.56.140

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000006

          Read_Master_Log_Pos: 765

               Relay_Log_File: relay-bin.000003

                Relay_Log_Pos: 931

        Relay_Master_Log_File: mysql-bin.000006

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB: testdb

          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: 765

              Relay_Log_Space: 1132

              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: 16f00eff-a1b7-11e7-ba2d-0800271962a3

             Master_Info_File: /usr/local/mysql-5.7.19-linux-glibc2.12-x86_64/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.00 sec)