首页 > 代码库 > mysql低版本的主从复制和5.7版本的主从复制

mysql低版本的主从复制和5.7版本的主从复制


mysql的主从复制和单向异步复制:

mysqlAB复制:

mysql 数据库的版本,两个数据库版本要相同,或者 slave 比 master 版本高!

# yum install mysql mysql-server -y

master server 配置

1)配置 /etc/my.cnf 配置文件

[mysqld]下添加一下参数

log-bin=mysql-bin #启动二进制日志系统

binlog-do-db=test #二进制需要同步的数据库名,如果需要同步多个库,例如要再同步 westos

,再添加一行“binlog-do-db=westos”,以此类推

server-id=1

#必须为 到 232–1 之间的一个正整数值

binlog-ignore-db=mysql #禁止同步 mysql 数据库

2 ) 启动 master

# service mysqld start

3)创建同步帐户,并给予权限

mysql> GRANT REPLICATION SLAVE ON *.* to ginkgo@‘172.25.28.2‘ identified by ‘redhat‘;

mysql> flush privileges;

mysql> show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000005 |      854 | test,ginkgo  | mysql            |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

slave1server 配置

1 )配置 /etc/ my.cnf 文件

[mysqld]下添加一下参数

server-id=2

#从服务器 ID ,不要和主 ID 相同,如果设置多个从服务器,每个从服务器必

须有一个唯一的 server-id ,必须与主服务器的以及其它从服务器的不相同。

可以认为 server-id 值类似于 IP 地址:这些 ID 值能唯一识别复制服务器群集

中的每个服务器实例。

2 ) 启动 slave

# service mysqld start

3) 在 slave1 上执行一下命令

mysql> change master to master_host=‘172.25.28.1‘,master_user=‘ginkgo‘,master_password=‘redhat‘,master_log_file=‘mysql-bin.000005‘,master_log_pos=420;

Query OK, 0 rows affected (0.05 sec)

mysql> slave start;

Query OK, 0 rows affected (0.00 sec)

 

mysql> show slave status \G;

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 172.25.28.1

                  Master_User: ginkgo

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000005

          Read_Master_Log_Pos: 420

               Relay_Log_File: mysqld-relay-bin.000002

                Relay_Log_Pos: 251

        Relay_Master_Log_File: mysql-bin.000005

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB: 

          Replicate_Ignore_DB: 

 

如果都是 yes,表示从库的 I/O,Slave_SQL 线程都正确开启.表明数据库正在同步

[root@server1 mysql]# ls

backup2.db            ibdata1      mysql-bin.000001  mysql-bin.000005

backup.db             ib_logfile0  mysql-bin.000002  mysql-bin.index

dashboard_production  ib_logfile1  mysql-bin.000003  mysql.sock

ginkgo                mysql        mysql-bin.000004

[root@server1 mysql]# 

相关文件作用:

1. mysql-bin.index: 服务器一旦开启二进制日志,会产生一个与二日志文件同名,但是以.index 结尾

的文件。它用于跟踪磁盘上存在哪些二进制日志文件。MySQL 用它来定位二进制日志文件。

2. mysqld-relay-bin.index: 该文件的功能与 mysql-bin.index 类似,但是它是针对中继日志,而不是

二进制日志。

3. master.info :保存 master 的相关信息。不要删除它,否则,slave 重启后不能连接 master

4. relay-log.info :包含 slave 中当前二进制日志和中继日志的信息如果写操作较少,而读操作很多时,可以采取这种结构。你可以将读操作分布到其它的 slave,从而减小

master 的压力。但是,当 slave 增加到一定数量时,slave 对 master 的负载以及网络带宽都会成为一个严重的问题。这种结构虽然简单,但是,它却非常灵活,足够满足大多数应用需求

当设置 log_slave_updates ,你可以让 slave 扮演其它 slave 的 master。此时,slave 把 SQL 线程执行的事件写进行自己的二进制日志(binary log),然后,其它的 slave 可以获取这些事件并执行它,从而有效缓解master 的压力

 

添加一个 slave2:

1. 由于 master 上已经有数据,而新加的 slave2 没有,必须在配置复制前同步数据。

1)在 master 上执行一下命令

mysql> FLUSH TABLES WITH READ LOCK;

#锁表

[root@server1 mysql]# mysqldump --all-databases --lock-all-tables >backup.db

#备份

mysql> UNLOCK TABLES;

#表解锁

 

 

或:

[root@server1 mysql]# mysqlbinlog mysql-bin.000005

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#170310 14:30:57 server id 1  end_log_pos 106 Start: binlog v 4, server v 5.1.71-log created 170310 14:30:57 at startup

# Warning: this binlog is either in use or was not closed properly.

ROLLBACK/*!*/;

BINLOG ‘

...

# at 106

#170310 15:17:06 server id 1  end_log_pos 258 Querythread_id=4exec_time=0error_code=0

SET TIMESTAMP=1489130226/*!*/;

SET @@session.pseudo_thread_id=4/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;

SET @@session.sql_mode=0/*!*/;

mysqlbinlog --start-datetime=”2017-03-10 14:30:57 ” mysql-bin-00005 > backup.sql

scp backup.db 172.25.28.3

[root@server1 mysql]# scp backup.db 172.25.28.3:

2. 在 slave1 上加入以下设置:

# vi /etc/my.cnf

....

server-id=2

log-bin=mysql-bin

binlog-ignore-db=mysqlbinlog-do-db=test

log-slave-updates

....

# /etc/init.d/mysqld restart

3. 在 slave1 上创建同步帐户,并给予权限

 

mysql> grant replication slave on *.* to ginkgo@‘172.25.28.3‘ identified by ‘redhat‘;

Query OK, 0 rows affected (0.00 sec)

 

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

 

mysql> show master status \G

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

            File: mysql-bin.000001

        Position: 333

    Binlog_Do_DB: test,ginkgo

Binlog_Ignore_DB: mysql

1 row in set (0.00 sec)

 

mysql> show master status 

    -> ;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000001 |      333 | test,ginkgo  | mysql            |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

4. 在 slave2 上添加配置:

# vi /etc/my.cnf

server-id=3

# /etc/init.d/mysqld start

5. 在 slave2 上执行一下命令

mysql> change master to master_host = ‘172.25.28.2‘,master_user=‘ginkgo‘,master_password=‘redhat‘,master_log_file=‘mysql-bin.000001‘,master_log_pos=333;

Query OK, 0 rows affected (0.04 sec)

 

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

 

mysql> show slave status \G;

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 172.25.28.2

                  Master_User: ginkgo

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000001

          Read_Master_Log_Pos: 333

               Relay_Log_File: mysqld-relay-bin.000002

                Relay_Log_Pos: 251

        Relay_Master_Log_File: mysql-bin.000001

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              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: 

 

高版本的主从复制:

 

搭建mysql的主从复制:

确保系统上没有低版本的的mysql软件及其文件:

rpm -qa|grep mysql

清除所有的安装mysqlrm -fr /var/lib/mysql

首先下载tar包,编译安装mysql

mysql5.7启动后会在系统自动生成一个复杂的密码,在其日志文件中记录着,通过

grep password /var/log/mysqlog抓取密码,然后登陆,登陆后第一件事是更改密码使用

mysql> alter user root@‘localhost‘ identified by ‘Ginkgo+007‘;

Query OK, 0 rows affected (0.05 sec)

然后即可使用自己设定的密码登陆,对于slave作同样的事情。

修改配置文件文件:

[root@server1 ~]# cat /etc/my.cnf

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

 

[mysqld]

server-id=1

log-bin=mysql-bin

binlog-do-db=ginkgo

binlog-ignore-db=mysql

gtid-mode=ON/打开gtid模式

enforce-gtid-consistency=1  

#

 

[root@server2 ~]# cat /etc/my.cnf

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

 

[mysqld]

server-id=2

gtid-mode=ON

enforce-gtid-consistency=1

 

#

重新启动mysql服务(配置文件作出更改后要重起服务,除非使用热修改,即使用mysql语句修改,在生产中一般都是使用sql语句进行。)

server1.example.com 中:

对从服务器授权:

mysql> grant replication slave on *.* to ginkgo@‘172.25.33.2‘ identified by ‘Ginkgo+007‘;

Query OK, 0 rows affected, 1 warning (0.06 sec)

server2.example.com中添加主服务器:

mysql> change master to master_host=‘172.25.33.1‘,master_user=‘ginkgo‘,master_password=‘Ginkgo+007‘,master_auto_position=1;

Query OK, 0 rows affected, 2 warnings (0.53 sec)

 

启动slave

server2上执行:

start slave;

mysql> start slave;

Query OK, 0 rows affected (0.03 sec)

mysql> show slave status\G;

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 172.25.33.1

                  Master_User: ginkgo

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000004

          Read_Master_Log_Pos: 601

               Relay_Log_File: server2-relay-bin.000002

                Relay_Log_Pos: 814

        Relay_Master_Log_File: mysql-bin.000004

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

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

              Relay_Log_Space: 1023

              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: f8c6f53c-08ab-11e7-b323-5254000b91d2

             Master_Info_File: /var/lib/mysql/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: f8c6f53c-08ab-11e7-b323-5254000b91d2:1-2

            Executed_Gtid_Set: f8c6f53c-08ab-11e7-b323-5254000b91d2:1-2

                Auto_Position: 1

         Replicate_Rewrite_DB: 

                 Channel_Name: 

           Master_TLS_Version: 

1 row in set (0.00 sec)

 

ERROR: 

No query specified

 

 

master上添加数据库,观察从上的动静:

mysql> create database ginkgo;

Query OK, 1 row affected (0.05 sec)

 

mysql> create table ginkgo.userdb;

ERROR 1113 (42000): A table must have at least 1 column

mysql> create table ginkgo.userdb(

    -> username varchar(20) not null,

    -> password varchar(20) not null);

Query OK, 0 rows affected (0.42 sec)

 

mysql> desc ginkgo.userdb;

+----------+-------------+------+-----+---------+-------+

| Field    | Type        | Null | Key | Default | Extra |

+----------+-------------+------+-----+---------+-------+

| username | varchar(20) | NO   |     | NULL    |       |

| password | varchar(20) | NO   |     | NULL    |       |

+----------+-------------+------+-----+---------+-------+

2 rows in set (0.01 sec)

 

server2.example.com中执行

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| ginkgo             |

| mysql              |

| performance_schema |

| sys                |

+--------------------+

5 rows in set (0.00 sec)

 

mysql> 

mysql> show slave status \G;

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 172.25.33.1

                  Master_User: ginkgo

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000004

          Read_Master_Log_Pos: 992

               Relay_Log_File: server2-relay-bin.000002

                Relay_Log_Pos: 1205

        Relay_Master_Log_File: mysql-bin.000004

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

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

              Relay_Log_Space: 1414

              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: f8c6f53c-08ab-11e7-b323-5254000b91d2

             Master_Info_File: /var/lib/mysql/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: f8c6f53c-08ab-11e7-b323-5254000b91d2:1-4

            Executed_Gtid_Set: f8c6f53c-08ab-11e7-b323-5254000b91d2:1-4

                Auto_Position: 1

         Replicate_Rewrite_DB: 

                 Channel_Name: 

           Master_TLS_Version: 

1 row in set (0.00 sec)

 

ERROR: 

No query specified

 

没有错误。

mysql> show processlist;

+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+

| Id | User        | Host      | db   | Command | Time | State                                                  | Info             |

+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+

|  7 | system user |           | NULL | Connect | 1305 | Waiting for master to send event                       | NULL             |

|  8 | system user |           | NULL | Connect |  489 | Slave has read all relay log; waiting for more updates | NULL             |

| 11 | root        | localhost | NULL | Query   |    0 | starting                                               | show processlist |

+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+

3 rows in set (0.00 sec)

 

 


mysql低版本的主从复制和5.7版本的主从复制