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.