Mysql主从配置

Mysql主从配置

主从介绍

主从：很多人也叫做AB复制

官方称为：replication 从一个主把日志复制到从上，从上根据这个日志执行相应的操作，从而达到，两个数据库的数据保持一致。

A – change data – > bin_log -transfer-> B– repl_log –> change
database MySQL主从是基于binlog的，主上须开启binlog才能进行主从。

---

主从过程

1,主将更改操作记录到binlog中 ;
2,从将主的binlog事件（SQL语句）同步到本机并记录在relaylog中;
3,从根据relaylog里面的SQL语句按顺序执行;

说明： 该过程有三个线程，主上有一个log dump线程，用来和从的i/o线程传递binlog；从上有两个线程，其中i/o线程用来同步主的binlog并生成relaylog，另外一个SQL线程用来把relaylog里面的SQL语句落地。

---

Mysql主从工作原理

---

应用环境

备份重要数据 ；
分担主库数据读取压力；

---

工作准备

主：192.168.96.128
从：192.168.96.127
两台服务器需安装Mysql并启动服务。

---

配置主服务器

编辑配置文件添加如下参数

[root@dl-001 ~]# vim /etc/my.cnf    //添加

server-id=128   # 设置128为主，127为从，注意主从的id不要相同
log_bin=dl1     ##指定log前缀

---

重启mysql服务

[root@dl-001 ~]# /etc/init.d/mysqld restart

Shutting down MySQL.. SUCCESS! 
Starting MySQL.. SUCCESS!

查看mysql库文件：

[root@dl-001 ~]# cd /data/mysql/
[root@dl-001 mysql]# ls -lt
总用量 176228
-rw-rw----. 1 mysql mysql 50331648 1月  18 15:45 ib_logfile0
-rw-rw----. 1 mysql mysql 79691776 1月  18 15:45 ibdata1
-rw-rw----. 1 mysql mysql    58478 1月  18 15:45 dl-001.err
-rw-rw----. 1 mysql mysql        5 1月  18 15:45 dl-001.pid
-rw-rw----. 1 mysql mysql       13 1月  18 15:45 dl1.index
-rw-rw----. 1 mysql mysql      120 1月  18 15:45 dl1.000001
drwx------. 2 mysql mysql    12288 1月  11 15:54 zabbix
-rw-rw----. 1 mysql mysql       56 12月 14 22:41 auto.cnf
drwx------. 2 mysql mysql     4096 12月 14 22:35 mysql
drwx------. 2 mysql mysql     4096 12月 14 22:35 performance_schema
-rw-rw----. 1 mysql mysql 50331648 12月 14 22:35 ib_logfile1
drwx------. 2 mysql mysql        6 12月 14 22:35 test

说明： 重启后生成两个前缀为dl1的二进制文件。

---

试验准备:新建一个数据库

#备份一个数据库
[root@dl-001 mysql]# mysqldump -uroot -p'mysqldl991124' test > /tmp/test.sql
Warning: Using a password on the command line interface can be insecure.
[root@dl-001 mysql]# du -sh /tmp/test.sql 
4.0K test.sql

#新建一个数据库
[root@dl-001 mysql]# mysql -uroot -p'mysqldl991124' -e "create database dltest"
Warning: Using a password on the command line interface can be insecure.

#将备份的数据恢复到新建的数据库中
[root@dl-001 mysql]# mysql -uroot -p'mysqldl991124' dltest < /tmp/test.sql 
Warning: Using a password on the command line interface can be insecure.

---

创建一个用于同步数据的用户

[root@dl-001 mysql]# mysql -uroot -p'mysqldl991124'
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 7
Server version: 5.6.35-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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 'repl'@'192.168.96.127' identified by 'testdl991124';  //创建用户。（IP为“从”的IP）
Query OK, 0 rows affected (0.00 sec)
mysql> flush tables with read lock;   //锁定数据表（目的是暂时使其不能继续写，保持现有状态用于同步）     
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;      //记住file和position(设置主从同步时会使用)
+------------+----------+--------------+------------------+-------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+------------------+-------------------+
| dl1.000001 |      431 |              |                  |                   |
+------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> quit
Bye

---

备份主库中所有数据库

[root@dl-001 mysql]# ls
auto.cnf    dl-001.pid  dl1.index  ibdata1      ib_logfile1  performance_schema  zabbix
dl-001.err  dl1.000001  dltest     ib_logfile0  mysql        test
[root@dl-001 mysql]# mysqldump -uroot -p'mysqldl991124' zabbix > /tmp/zabbix.sql
Warning: Using a password on the command line interface can be insecure.

---

配置从服务器

添加配置文件

[root@dl-002 ~]# vim /etc/my.cnf
server-id=127

说明： 从的server-id要和主的server-id不同。

---

重启

[root@dl-002 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.. SUCCESS! 

---

配置完成后将主中备份的数据发送到从中

[root@dl-002 ~]# scp 192.168.96.128:/tmp/*.sql /tmp/
root@192.168.96.128's password: 
test.sql                                                 100% 1258     1.2KB/s   00:00    
zabbix.sql                                               100% 1414KB   1.4MB/s   00:00

[root@dl-002 ~]# ls /tmp       //查看
hsperfdata_root                                                           test.sql
mysql.sock                                                                zabbix.sql
systemd-private-929ff93f14ea4a4a9bbb19a64dfdd55f-vmtoolsd.service-ftrUel

---

创建库

mysql> create database dltest;
Query OK, 1 row affected (0.00 sec)

mysql> create database zabbix;
Query OK, 1 row affected (0.00 sec)



mysql> quit
Bye

---

恢复数据库

[root@dl-002 ~]# mysql -uroot -p'mysqldl991124' dltest< /tmp/test.sql 
Warning: Using a password on the command line interface can be insecure.
[root@dl-002 ~]# mysql -uroot -p'mysqldl991124' zabbix < /tmp/zabbix.sql 
Warning: Using a password on the command line interface can be insecure.

注意： 该过程要保证主从数据库内容的一致。

---

实现主从同步

[root@dl-002 ~]# mysql -uroot -p'mysqldl991124'
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 13
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)




mysql> change master to master_host='192.168.96.128',master_user='repl',master_password='mysqldl991124',master_log_file='dl1.000001',master_log_pos=431;    //IP为主的IP；file、pos分别为主的filename和position。
Query OK, 0 rows affected, 2 warnings (0.07 sec)


mysql> start slave;         //检测主从是否建立成功
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G        //查看是否主从配置成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

---

以上操作完成后，解锁主库的表（在主上的操作）

[root@dl-001 mysql]# mysql -uroot -p'mysqldl991124'        //在主机器上恢复写操作
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 9
Server version: 5.6.35-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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库上进行操作，不然容易造成主mysql库的紊乱
说明：此时已经配置成功，在主上操作，从也会随之更改！！！

---

参数介绍

主服务器（在/etc/my.cnf中配置）:

binlog-do-db=               //类似白名单，将需要同步的库添进去
binlog-ignore-db=           //类似黑名单，将不需要同步的库列出来

从服务器（在/etc/my.cnf中配置）：

replicate_do_db=                //同步指定的库
replicate_ignore_db=        //忽略指定的库
replicate_do_table=         //同步指定的表
replicate_ignore_table=         //忽略指定的表

replicate_wild_do_table=    //如aming.%，支持通配符    
replicate_wild_ignore_table=    

总结：主从配置经常容易中断，建议及时监控，在从mysql上关注命令show slave status\G;
另外，千万别在从mysql上去写入数据，很容易会导致主mysql的紊乱

建议: MySQL主从机制比较脆弱，谨慎操作。如果重启master主mysql库，务必要先把slave停掉，也就是说需要在slave上去执行 slave stop 命令，然后再去重启
master的mysql服务，否则很有可能就会中断了。当然重启完后，还需要把slave给开启 slave start.