2017-10-13

mysql 主从搭建

mysql 主从（mysql replication），主要用于 mysql 的实时备份或者读写分离。在配置之前做准备工作，配置两台 mysql 服务器；或者在同一个机器上跑两个 mysql 服务。

mysql 主从原理非常简单:

每个从仅可以设置一个主；
主在执行 sql 之后，记录二进制 log 文件（bin-log）；
从连接主，并从主获取 binlog ，存于本地 relay-log ，并从上次记住的位置起执行 sql，一旦遇到错误则停止同步。

从这几条 replication 原理来看，可以推论：

主从间的数据库不是实时同步，就算网络正常连接，也存在瞬间，主从数据不一致；
如果主从网络断开，从会在网路正常后，批量同步；
如果对从进行修改数据，那么很可能从在执行主的 bin-log 时出现错误而停止同步，这是很危险的操作。所以一般情况下，非常小心的修改从上的数据。

一个衍生的配置是双主，互为主从配置，只要双方不修改冲突，可以工作良好。

如果需要多主的话，可以用环形配置，这样任意一个节点的修改都可以同步到所有节点。

可以应用在读写分离的场景中，用以降低单台 mysql 服务器的 I/O

可以实现 mysql 服务的 HA 集群。

可以是一主多从，也可以是相互主从（主主）。

在同一台机器上配置 MySQL 主从

安装、配置 mysql（mysql版本5.1.40）

首先在同一台机器上配置两个 MySQL 服务（跑两个端口）

1 2	[root@192 ~]# cd /usr/local [root@192 local]# cp -r mysql mysql_slave

拷贝配置文件

1 2	[root@192 local]# cd mysql_slave/ [root@192 mysql_slave]# cp /etc/my.cnf ./my.cnf

修改配置文件相关参数

1	[root@192 mysql_slave]# vim my.cnf

更改 port 为 3307 ， sock 为 /tmp/mysql_slave.sock 以及 datadir

初始化 mysql_slave

1	[root@192 mysql_slave]# ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql_slave

启动准备

1 2	[root@192 mysql_slave]# cp /etc/init.d/mysqld /etc/init.d/mysqldslave [root@192 mysql_slave]# vim /etc/init.d/mysqldslave

修改

修改为

最好在前边添加

启动

1	[root@192 mysql_slave]# /etc/init.d/mysqldslave start

配置主从准备工作

设定 /usr/local/mysql 为主，端口 3306，/usr/local/mysql_slave 为从，端口为 3307 。

登录主 mysql

1	[root@192 ~]# mysql -S /tmp/mysql.sock

或者

1	[root@192 ~]# mysql -h127.0.0.1 -P3306

登录从 mysql

1	[root@192 ~]# mysql -S /tmp/mysql_slave.sock

或者

1	[root@192 ~]# mysql -h127.0.0.1 -P3307

在主上创建测试数据库

1	[root@192 ~]# mysql -S /tmp/mysql.sock

创建数据库 db1

1 2	mysql> create database db1; Query OK, 1 row affected (0.03 sec)

然后导出主的 mysql 数据库谈后导入给 db1

1 2	[root@192 ~]# mysqldump -S /tmp/mysql.sock mysql > 1.sql [root@192 ~]# mysql -S /tmp/mysql.sock db1 < 1.sql

配置主（master）

1	[root@192 ~]# vim /etc/my.cnf

修改或添加：

两个可选参数（二选一）

1 2	binlog-do-db=db1 #用来指定需要同步的库，这里只同步 db1 binlog-ignore-db=db1,db2 #指定忽略不同步的库，这里不同步 db1,db2

修改配置文件后，重启 mysql

1
2
3

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

设置 root 密码

1 2	[root@192 ~]# mysqladmin -uroot -S /tmp/mysql.sock password '123456' [root@192 ~]# mysql -uroot -S /tmp/mysql.sock -p123456

然后授权给从一个用来同步数据的用户 repl

1	mysql> grant replication slave on . to 'repl'@'127.0.0.1' identified by '123123';

只给授权 replication 权限；用户名 repl ；由于是本机，所有用回还地址；密码是123123

重新刷新权限

1	mysql> flush privileges;

把表的读锁死

1	mysql> flush tables with read lock;

要记住前两列的内容，后面会用到

1	mysql> show master status;

设置从

1	[root@192 ~]# vim /usr/local/mysql_slave/my.cnf

修改或添加

这个数值不能和主一样

可选参数

1 2	replicate-do-db=db1,db2 replicate-ignore-db=db1,db2

意义同主的那两个可选参数，如果主定义过了，那么从上就不用再次加这些参数了。

重启从的 mysqld 服务

1
2
3

[root@192 ~]# /etc/init.d/mysqldslave restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

在从上创建 db1 数据库，拷贝住的 db1 数据库到从

1 2	[root@192 ~]# mysql -S /tmp/mysql_slave.sock -e "create database db1" [root@192 ~]# mysql -S /tmp/mysql_slave.sock db1 < 1.sql

登录从 mysql

1	[root@192 ~]# mysql -S /tmp/mysql_slave.sock

执行命令

1	mysql> slave stop;

1	mysql> change master to master_host='127.0.0.1', master_port=3306, master_user='repl', master_password='123123', master_log_file='mysql-bin.000001', master_log_pos=453

指定主的 host 127.0.0.1 ；主的 post 3306；读主的用户 repl ，密码 123123；

1	mysql> slave start;

查看配置是否成功

slave 两个显示都是 YES 表示成功

测试主从

解锁表的读

1	mysql> unlock tables;

在主上删除一个表

1
2
3

mysql> use db1;
Database changed
mysql> show tables;

删除表 help_category

1	mysql> drop table help_category;

再到从上查看

在主上清空 db1 库的 db 表

1
2
3

mysql> use db1
mysql> select count(*) from db;

1	mysql> truncate table db;

进入 slave ，查看 db1 库 db 表

得出的结果为 0 了，说明主从是同步的。

然后在主上删除表 db

1	mysql> drop table db;

在从上看 db 表不存在了

也可以创建一个表

1	mysql> show create table user\G;

复制这个表，然后创建表

1	mysql> show tables;

查看发现多了表 yanyi

从上查看

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

在两台机器上配置 MySQL 主从

安装、配置 MySQL （mysql 版本5.7.17）

配置主从准备工作

两台机器 192.168.0.72 和192.168.0.71

设定 192.168.0.72 为主 192.168.0.71 为从

在主上创建测试数据库

1	[root@master ~]# mysql -uroot -p123456

1	mysql> create database db1;

或者

1	[root@master ~]# mysql -uroot -p123456 -e "create database db1;"

然后导出主的 mysql 库数据，然后导入给 db1

1 2	[root@master ~]# mysqldump -uroot -p123456 mysql > 1.sql [root@master ~]# mysql -uroot -p123456 db1 < 1.sql

配置主（master）

1	[root@master ~]# vim /etc/my.cnf

修改为

两个可选参数（二选一）

1 2	binlog-do-db=db1 #用来指定需要同步的库，这里只同步 db1 binlog-ignore-db=db1,db2 #指定忽略不同步的库，这里不同步 db1,db2

修改配置文件后，重启 mysql

1
2
3

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

然后授权给从一个用来同步数据的用户 repl

1	mysql> grant replication slave on . to 'repl'@'192.168.0.71' identified by '123123';

只给授权 replication 权限；用户名 repl ；授权机器 IP 192.168.0.71；密码是123123

重新刷新权限

1	mysql> flush privileges;

把表的读锁死

1	mysql> flush tables with read lock;

要记住前两列的内容，后面会用到

1	mysql> show master status;

设置从（slave）

1	[root@slave ~]# vim /etc/my.cnf

修改为

这个数值不能和主一样

可选参数

1 2	replicate-do-db=db1,db2 replicate-ignore-db=db1,db2

意义同主的那两个可选参数，如果主定义过了，那么从上就不用再次加这些参数了。

重启从的 mysqld 服务

1
2
3

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

在从上创建 db1 数据库，拷贝主的 db1 数据库到从

1 2	[root@slave ~]# mysql -uroot -p123456 -e "create database db1;" [root@slave ~]# mysql -uroot -p123456 db1 < 1.sql

登录从 mysql

1	[root@slave ~]# mysql -uroot -p123456

执行如下命令

1	mysql> stop slave;

1	mysql> change master to master_host='192.168.0.72', master_port=3306, master_user='repl', master_password='123123', master_log_file='mysql-bin.000001', master_log_pos=600;

指定主的 host 192.168.0.72 ；主的 post 3306；读主的用户 repl ，密码 123123；