linux 学习笔记-059-Mysql-Mysql 主从

发布于 2018-04-02  328 次阅读


什么是 Mtsql 主从?

MySQL 主从又叫做 Replication、AB 复制。简单讲就是 A 和 B 两台机器做主从后,在 A 上写数据,另外一台 B 也会跟着写数据,两者数据实时同步的

MySQL 主从是基于 binlog 的,主上须开启 binlog 才能进行主从。

主从过程大致有 3 个步骤

1)主将更改操作记录到 binlog 里

2)从将主的 binlog 事件(sql 语句)同步到从本机上并记录在 relaylog 里

3)从根据 relaylog 里面的 sql 语句按顺序执行

主上有一个 log dump 线程,用来和从的 I/O 线程传递 binlog

从上有两个线程,其中 I/O 线程用来同步主的 binlog 并生成 relaylog,另外一个 SQL 线程用来把 relaylog 里面的 sql 语句落地

使用场景:

01:数据需要备份的时候

02:除了数据备份外,还达到一个负载均衡的目的,减轻主库读的压力,但需要注意的是只有主才能做写操作,主和从都能做读操作

准备工作

主服务器和从服务器均安装好 Mysql 并启动起来(安装步骤请看:https://www.itwordsweb.com/?s=mysql

这里使用 Mysql 5.6 版本

[root@am-01:~#] ps -aux | grep mysql

root       5176  0.0  0.1  11776  1108 ?        S    3 月 22   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/am-01.pid

mysql      5314  0.1 32.7 1766876 328224 ?      Sl   3 月 22  20:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/am-01.err --pid-file=/data/mysql/am-01.pid --socket=/tmp/mysql.sock

root      26534  0.0  0.0 112676   972 pts/1    S+   23:06   0:00 grep --color=auto mysql

[root@am-01:~#] mysql -V

mysql  Ver 14.14 Distrib 5.6.36, for linux-glibc2.5 (x86_64) using  EditLine wrapper

#主服务器
[root@am-02:~#] ps -aux | grep mysql

root      17940  0.0  0.1  11776  1580 pts/0    S    00:09   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/am-02.pid

mysql     18047  0.1 44.8 1366572 449540 pts/0  Sl   00:09   0:05 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/am-02.err --pid-file=/data/mysql/am-02.pid

root      18204  0.0  0.0 112672   976 pts/0    S+   01:02   0:00 grep --color=auto mysql

[root@am-02:~#] mysql -V

mysql  Ver 14.14 Distrib 5.6.36, for linux-glibc2.5 (x86_64) using  EditLine wrapper

#从服务器

配置主服务器

[root@am-01:~#] vim /etc/my.cnf

  [mysqld]

  datadir=/data/mysql

  socket=/tmp/mysql.sock

  server-id=240

  log_bin=am01

[root@am-01:~#] service mysqld restart

Shutting down MySQL.... SUCCESS!

Starting MySQL....... SUCCESS!

[root@am-01:~#] cd /data/mysql/

[root@am-01:/data/mysql#] ls

am01.000001  am01.index  auto.cnf  ib_logfile0  mysql   performance_schema  zrlog

am-01.err    am-01.pid   ibdata1   ib_logfile1  mysql2  test

#修改主配置文件 my.cnf,server-id 和 log_bin,值可以随便取,记得即可

#重启 Mysql,可以见到/data/mysql/目录下生成了以 am01 开头的一些文件(这些文件很重要,是实现主从的根本)

#binlog 很重要,会记录着你对数据库的操作,其实你可以直接使用 binlog 来做数据库回复,前提是 binlog 记录的操作步骤是完整的
[root@am-01:/data/mysql#] mysqldump -uroot -pitsupport.0 zrlog > /tmp/zrlog.sql

Warning: Using a password on the command line interface can be insecure.

[root@am-01:/data/mysql#] mysql -uroot -pitsupport.0 -e "create database am"

Warning: Using a password on the command line interface can be insecure.

[root@am-01:/data/mysql#] mysql -uroot -pitsupport.0 am < /tmp/zrlog.sql

Warning: Using a password on the command line interface can be insecure.

[root@am-01:/data/mysql#] mysqldump -uroot -pitsupport.0 mysql2 > /tmp/mysql2.sql

Warning: Using a password on the command line interface can be insecure.

[root@am-01:/data/mysql#] ls /tmp/*.sql

tmp/mysql2.sql  /tmp/test.sql  /tmp/zrlog.sql

#把之前实验留下的 zrlog 库备份一下,并创建新库 am,再把备份的 zrlog.sql 恢复到新库 am 中

#同时也把 mysql2 和 test 的库也备份一下,之后拿来做实验
[root@am-01:/data/mysql#] mysql -uroot -pitsupport.0

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 354

Server version: 5.6.36-log MySQL Community Server (GPL)



Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.



Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.



Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.



mysql> grant replication slave on *.* to 'am'@'172.17.1.242' identified by 'itsupport.0';

Query OK, 0 rows affected (0.04 sec)



mysql> flush tables with read lock;

Query OK, 0 rows affected (0.01 sec)



mysql> show master status;

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

| File        | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| am01.000001 |    10267 |              |                  |                   |

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

1 row in set (0.00 sec)



mysql> quit

Bye

#创建一个新的用户,用来做主从的用户

#"flush tables with read lock;"是把数据库锁定,目的是为了不让其继续写,因为之后需要用从服务器备份,需要保持一致,这样的才能实现同步

#"show master status;",把 File 和 Position 的值记着,同时 File 的路径也需要记着,后面要用

配置从服务器

[root@am-02:~#] vim /etc/my.cnf

  basedir = /usr/local/mysql

  datadir = /data/mysql

  server_id = 242

  socket = /tmp/mysql.sock

[root@am-02:~#] service mysqld restart

Shutting down MySQL.. SUCCESS!

Starting MySQL..... SUCCESS!

#从服务器增加 server-id,要和主服务器区分开来,从服务器不需要设置 log_bin,重启一下 Mysql
[root@am-02:/tmp#] scp 172.17.1.240:/tmp/*.sql /tmp/

root@172.17.1.240's password:

mysql2.sql                                                                100%  642KB 642.1KB/s   00:00   

zrlog.sql                                                                 100% 9872     9.6KB/s   00:00   

#把主服务器备份的 sql 文件拉取到从服务器上
[root@am-02:~#] mysql -uroot

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.6.36 MySQL Community Server (GPL)



Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.



Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.



Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.



mysql> create database mysql2;

Query OK, 1 row affected (0.00 sec)



mysql> create database zrlog;

Query OK, 1 row affected (0.00 sec)



mysql> create database am;

Query OK, 1 row affected (0.00 sec)



mysql> quit

Bye

#从服务器也创建跟主服务器一样的库
[root@am-02:/tmp#] mysql -uroot am < /tmp/zrlog.sql

[root@am-02:/tmp#] mysql -uroot zrlog < /tmp/zrlog.sql

[root@am-02:/tmp#] mysql -uroot mysql2 < /tmp/mysql2.sql

#把从主服务器拷贝过来的 sql 文件恢复到对应数据库上,切记,从服务器和主服务器的数据要保持一致
mysql> stop slave;

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



mysql> change master to master_host='172.17.1.240', master_user='am', master_password='itsupport.0', master_log_file='am01.000001', master_log_pos=10267;



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.17.1.240

                  Master_User: am

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: am01.000001

          Read_Master_Log_Pos: 10267

               Relay_Log_File: am-02-relay-bin.000002

                Relay_Log_Pos: 278

        Relay_Master_Log_File: am01.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:

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 10267

              Relay_Log_Space: 451

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

                  Master_UUID: c0a7f5a5-2606-11e8-957d-000c2908ac45

             Master_Info_File: /data/mysql/master.info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

           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

1 row in set (0.00 sec)



[root@am-01:/tmp#] mysql -uroot -pitsupport.0

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 7042

Server version: 5.6.36-log MySQL Community Server (GPL)



Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.



Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.



Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.



mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)



mysql> quit

Bye

#"master_host"指定主服务器地址,"master_user"指定做主从的用户,"master_password"指定做主从的用户的密码,"master_llog_file"为主服务器的 File 列,master_log_pos 为主服务器的 Position 列

#切记要先确保主和从的数据一致,然后先"stop slave",最后"start slave;"

#最后,主服务器要把数据库解锁,那样才能实现同步

知识点:

01:使用"show slave stauts\G"看是否有

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

有的话一般主从已经设置成功

02:关注下面这些参数的值,用来判断主从的状态

Seconds_Behind_Master: 0  //为主从延迟的时间

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

配置参数介绍:

主服务器上

binlog-do-db=      //同步指定的库,多个库用逗号分割

binlog-ignore-db= //忽略指定库

从服务器上

replicate_do_db=

replicate_ignore_db=

replicate_do_table=

replicate_ignore_table=

#一般不用以上四个参数

replicate_wild_do_table=   //如 am.%, 支持通配符%

replicate_wild_ignore_table=

测试主从是否成功

[root@am-01:~#] mysql -uroot -pitsupport.0

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 7159

Server version: 5.6.36-log MySQL Community Server (GPL)



Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.



Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.



Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.



mysql> use am;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A



Database changed

mysql> show tables;

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

| Tables_in_am |

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

| comment      |

| link         |

| log          |

| lognav       |

| plugin       |

| tag          |

| type         |

| user         |

| website      |

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

9 rows in set (0.00 sec)



mysql> drop table am.tag;

Query OK, 0 rows affected (0.10 sec)



mysql> show tables;

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

| Tables_in_am |

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

| comment      |

| link         |

| log          |

| lognav       |

| plugin       |

| type         |

| user         |

| website      |

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

8 rows in set (0.00 sec)

#在主服务器上把 am 库的 tag 表删除
[root@am-02:~#] mysql -uroot

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 17

Server version: 5.6.36 MySQL Community Server (GPL)



Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.



Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.



Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.



mysql> use am;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A



Database changed

mysql> show tables;

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

| Tables_in_am |

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

| comment      |

| link         |

| log          |

| lognav       |

| plugin       |

| type         |

| user         |

| website      |

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

8 rows in set (0.00 sec)

#在从服务器上检查,可以发现,从服务器上的 tag 表也被删除了

知识点:

假如发生主从不一致,即"Slave_SQL_Running: No"的时候,可以先"stop slave",最后"start slave;",看能否修复,如果无法修复,就确保两边数据一致,怎么确保一致?从服务器重新从主服务器拉取 sql 备份,然后做恢复,简单来讲,就是重新来,如果数据确保一致了,从服务器就可以直接从"change master"开始

切记,主服务器的 Position 列的值很敏感,会经常变动,如果重新做主从,要先把主服务器锁表(也可以不锁表,后面会讲到),并记录好这个值

扩展

mysql 主从配置 uuid 相同错误解决:

https://www.2cto.com/database/201412/364479.html