mysql主从介绍
MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后,在A上写数据,另外一台B也会跟着写数据,两者数据实时同步。
MySQL主从是基于binlog的,主上须开启binlog才能进行主从。
主从过程:
- 主将更改操作记录到binlog中
- 从将主的binlog事件(SQL语句)同步到本机并记录在relaylog中
- 从根据relaylog里面的SQL语句按顺序执行
主从原理图:
该过程有三个线程,主上有一个log dump线程,用来和从的i/o线程传递binlog;从上有两个线程,其中i/o线程用来同步主的binlog并生成relaylog,另外一个SQL线程用来把relaylog里面的SQL语句落地
主从的使用场景:
- 备份重要数据
- 分担主库数据读取压力
准备工作
准备两台linux,都装上MySQL
安装MySQL:http://blog.csdn.net/aoli_shuai/article/details/78745984
问题:
[root@shuai-02 src]# wget http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz
-bash: wget: 未找到命令
解决:
用
[root@shuai-02 src]# yum install -y wget
问题2:
[root@shuai-02 mysql]# ./scripts/mysql_install_db –user=mysql –datadir=/data/mysql
-bash: ./scripts/mysql_install_db: /usr/bin/perl: 坏的解释器: 没有那个文件或目录
解决办法:安装Perl和perl-devel
[root@shuai-02 mysql]# yum install -y perl perl-devel
[root@shuai-02 mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql
FATAL ERROR: please install the following Perl modules before executing ./scripts/mysql_install_db:
Data::Dumper
解决方法,安装autoconf库
[root@shuai-02 mysql]# yum install -y autoconf
[root@shuai-02 mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql
Installing MySQL system tables..../bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
解决方法:
[root@shuai-02 mysql]# yum -y install libaio-devel
配置主
修改配置文件
[root@shuai-01 ~]# vi /etc/my.cnf
增加如下配置
server-id=135
log_bin=shuailinux1
完成配置后,重启MySQL服务
[root@shuai-01 ~]# /etc/init.d/mysqld restart
查看MySQL的库文件
[root@shuai-01 ~]# ls -lt /data/mysql
总用量 110712
-rw-rw----. 1 mysql mysql 50331648 1月 29 16:07 ib_logfile0
-rw-rw----. 1 mysql mysql 12582912 1月 29 16:07 ibdata1
-rw-rw----. 1 mysql mysql 92933 1月 29 16:07 shuai-01.err
-rw-rw----. 1 mysql mysql 5 1月 29 16:07 shuai-01.pid
-rw-rw----. 1 mysql mysql 21 1月 29 16:07 shuailinux1.index
-rw-rw----. 1 mysql mysql 120 1月 29 16:07 shuailinux1.000001
drwx------. 2 mysql mysql 4096 1月 28 22:28 zrlog
drwx------. 2 mysql mysql 4096 1月 17 01:00 mysql
drwx------. 2 mysql mysql 48 1月 17 00:46 db1
-rw-rw----. 1 mysql mysql 56 1月 4 21:20 auto.cnf
drwx------. 2 mysql mysql 4096 1月 4 21:08 performance_schema
-rw-rw----. 1 mysql mysql 50331648 1月 4 21:08 ib_logfile1
drwx------. 2 mysql mysql 6 1月 4 21:08 test
新建一个实验数据库
备份一个数据库
[root@shuai-01 mysql]# mysqldump -uroot -p111111 zrlog > /tmp/zrlog.sql
新建一个数据库
[root@shuai-01 mysql]# mysql -uroot -p111111 -e "create database shuai"
Warning: Using a password on the command line interface can be insecure.
将备份的数据恢复到新建的数据库中
[root@shuai-01 mysql]# mysql -uroot -p111111 shuai < /tmp/zrlog.sql
Warning: Using a password on the command line interface can be insecure.
创建一个用作同步数据的用户:
[root@shuai-01 mysql]# mysql -uroot -p111111
mysql> grant replication slave on *.* to 'repl'@192.168.176.134 identified by '111111';
Query OK, 0 rows affected (0.14 sec)
这里的IP为从机器的IP
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.10 sec)
锁定表,让其暂时不能写,保持现状用于同步
mysql> show master status;
+——————–+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————–+———-+————–+——————+——————-+
| shuailinux1.000001 | 10597 | | | |
+——————–+———-+————–+——————+——————-+
1 row in set (0.02 sec)
记住file 和position,同步时会用到。
退出数据库
备份数据库中所有的库
[root@shuai-01 mysql]# mysqldump -uroot -p111111 shuai > /tmp/shuai.sql
Warning: Using a password on the command line interface can be insecure.
配置从
编辑配置文件
[root@shuai-02 mysql]# vim /etc/my.cnf
server-id=134
注意:log_bin只在主上设置重点内容
重启
[root@shuai-02 mysql]# /etc/init.d/mysqld restart
Shutting down MySQL..... SUCCESS!
Starting MySQL............................................................... SUCCESS!
配置完成后,在从机器上连接主机器,获取数据库
[root@shuai-02 mysql]# scp 192.168.176.135:/tmp/*.sql /tmp/
root@192.168.176.135's password:
shuai.sql 100% 9906 9.7KB/s 00:00
zrlog.sql 100% 9906 9.7KB/s 00:00
创建库:
[root@shuai-02 mysql]# mysql -uroot
mysql> create database shuai;
Query OK, 1 row affected (0.16 sec)
mysql> create database zrlog;
Query OK, 1 row affected (0.06 sec)
恢复数据库:
[root@shuai-02 mysql]# mysql -uroot shuai < /tmp/shuai.sql
[root@shuai-02 mysql]# mysql -uroot zrlog < /tmp/zrlog.sql
这个过程,一定要先停止主数据库的服务,不让它写数据。以保证主从两边数据一致。
实现主从同步:
在从数据库上
[root@shuai-02 mysql]# mysql -uroot
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='192.168.176.135',master_user='repl',master_password='111111',master_log_file='shuailinux1.000001',master_log_pos=10597;
Query OK, 0 rows affected, 2 warnings (0.32 sec)
IP为主的IP;file、pos分别为主的filename和position
检查主从是否建立成功:
mysql> start slave;
Query OK, 0 rows affected (0.15 sec)
mysql> show slave status\G
这里的两个yes
主从完成后,去主数据库,解锁表。
[root@shuai-01 mysql]# mysql -uroot -p111111
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
到这里,主从搭建完成。
测试主从
几个配置参数介绍
主服务器:
binlog-do-db= //仅同步指定的库,多个库,用“,”分割
binlog-ignore-db= //忽略指定的库
从服务器:
replicate_do_db= 同步指定的库
replicate_ignore_db= 忽略指定的库
replicate_do_table= 同步指定的表
replicate_ignore_table= 忽略指定的表
使用最多,好用,支持库.表
replicate_wild_do_table= 如aming.%,支持通配符
replicate_wild_ignore_table=
主数据库:
mysql> use shuai
mysql> show tables;
mysql> select count(*) user;
+------+
| user |
+------+
| 1 |
+------+
1 row in set (0.04 sec)
删除后
mysql> drop tables website;
Query OK, 0 rows affected (0.23 sec)
从数据库:
mysql> use shuai;
mysql> select count(*) user;
+------+
| user |
+------+
| 1 |
+------+
1 row in set (0.06 sec)
删除表后:
mysql> show tables;
+-----------------+
| Tables_in_shuai |
+-----------------+
| comment |
| link |
| log |
| lognav |
| plugin |
| tag |
| type |
| user |
+-----------------+
8 rows in set (0.00 sec)
在主从数据库时,只有在主数据库上删除,修改,从数据库是可以同步的,在从数据库上删除修改,就会使主从不同步,主从就会断开,只能从新做主从(change master)。