MySQL replication 主从配置

MySQL Replication 又叫做AB复制或者主从复制。它主要用于MySQL的时时备份或者读写分离。在配置之前先做一下准备工作，配置两台mysql服务器，或者在一台服务器上配置两个端口也可以。我是用一台服务器运行两个MySQL 。

配置 MySQL 服务

假如你已经搭建好了一个mysql，跑的是3306端口，下面再搭建一个3307端口的mysql:

# cd /usr/local/

# cp -r mysql mysql_2/

# cp support-files/my-large.cnf /etc/my.cnf

进入 mysql_2 目录，编译下：

# cd mysql_2/

# ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql_2

复制配置文件到mysql_2下，并修改相关内容：

# cp /etc/my.cnf ./my.cnf

# vim my.cnf

把其中的 “ port= 3306 ” 修改为 “port = 3307”，把 “ socket = /tmp/mysql.sock ” 改为 “ socket= /tmp/mysql2.sock ”，然后在这一行下面再增加一行“ datadir=/data/mysql_2”。

启动 mysql_2 ，如果你想重启只有先杀死进程 killall mysqld ，再按照下面的方法启动：

# cd /usr/local/mysql_2/bin/

# ./mysqld_safe --defaults-file=../my.cnf --user=mysql &

如果以后想开机启动，就把它加入/etc/rc.d/rc.local文件中:

# echo "cd /usr/local/mysql_2/bin/; ./mysqld_safe --defaults-file=../my.cnf --user=mysql &" >> /etc/rc.local

配置Replication

进入 mysql_2 数据库，下面两种方法都可以：

# mysql -uroot -h127.0.0.1 -P3307

# mysql -uroot -S /tmp/mysql2.sock

-S 后面指定mysql的socket文件路径，这也是登陆mysql的一种方法，因为在一台服务器上跑了两个mysql端口，所以，只能用 -S 这样的方法来区分。

mysql> create database db1;

创建个 db1 库，然后把mysql 库的数据复制给db1库：

# mysqldump -uroot -S /tmp/mysql2.sock mysql > 1.sql

# mysql -uroot -S /tmp/mysql2.sock db1<1.sql

设置主( master )

首先修改配置文件：

# vim /usr/local/mysql_2/my.cnf

在[mysqld]部分查看是否有以下内容，如果没有则添加:

server-id = 1

log-bin=mysql-bin

另外还有两个参数你可以选择性的使用（二选一）：

binlog-do-db=db1,db2 #需要同步的库
binlog-ignore-db=db1,db2 #忽略不同步的库

binlog-do-db=需要复制的数据库名，多个数据库名，使用逗号分隔。binlog-ignore-db=不需要复制的数据库库名，多个数据库名，使用逗号分隔。这两个参数其实用一个就可以啦。

如果修改过配置文件需要重启mysqld服务，否则不需要重启:

# ps aux |grep mysql2.sock |grep -v grep |awk '{print $2}' |xargs kill

# ./mysqld_safe --defaults-file=../my.cnf --user=mysql &

然后你在 /data/mysql_2/ 目录下会生成 mysql-bin.* 的文件，那是因为你配置文件中加入了 bin-log 。

设置 mysql 数据库 root 的访问密码：

# mysqladmin -uroot -S /tmp/mysql2.sock password '123456'

登陆 mysql ：

# mysql -uroot -S /tmp/mysql2.sock -p123456

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

这里的 repl 是为 slave 端设置的访问 master 端 mysql 数据的用户，密码为 123123 ，这里的 127.0.0.1 为 slave 的 ip (因为阿铭配置的 master 和 slave 都在本机)。

mysql> flush tables with read lock; //锁定数据库，此时不允许更改任何数据

mysql> show master status; //查看状态，这些数据是要记录的，一会在slave端会用到。

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000011 |      106 |              | mysql            |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

设置从（ slave ）：

先修改配置文件：

# vim /etc/my.cnf

找到 “server-id = 1” 这一行，删除掉或者改为 “server-id = 2” 总之不能让这个id和master一样，否则会报错。另外在从上，你也可以选择性的增加如下两行，对应于主上增加的两行:

replicate-do-db=db1,db2

replicate-ignore-db=db1,db2 

然后拷贝master上的db1库的数据到slave上，因为master和slave都在一台服务器上，所以操作起来简单了很多，如果是不同的机器，可能就需要远程拷贝了：

# cd

# mysqldump -uroot -S /tmp/mysql2.sock -p123456 db1 > db1.sql

# mysql -uroot -p123456 -e "create database db1"

# mysql -uroot -p123456 db1<db1.sql

拷贝完数据后，就需要在slave上配置主从了：

# mysql -uroot -p123456

mysql> slave stop;

mysql> change master to master_host='127.0.0.1', master_port=3307, master_user='repl', master_password='123123', master_log_file= 'mysql-bin.000011',master_log_pos=106;

mysql> slave start; //启动slave

其中master_log_file和master_log_pos是在上面使用 show master status; 查到的数据。执行完这一步后，需要在master上执行一步:

# mysql -uroot -S /tmp/mysql2.sock -p123456 -e "unlock tables" 
unlock tables 表示解锁数据库然后查看 slave 的状态：

mysql> show slave status\G;

确定下面两项参数都为 Yes ：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

测试主从：

登陆主：

# mysql -uroot -p123456

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

mysql> truncate table db; //清空表在主上操作在从上 select count(*) from db; 查看

mysql> drop table db; //主删除表，从查看：

mysql> select count(*) from db;
ERROR 1146 (42S02): Table 'db1.db' doesn't exist

主从配置起来很简单，但是这种机制也是非常脆弱的，一旦我们不小心在从上写了数据，那么主从也就被破坏了。另外如果重启master，务必要先把slave停掉，也就是说需要在slave上去执行 slave stop 命令，然后再去重启master的mysql服务，否则很有可能就会中断了。当然重启完后，还需要把slave给开启 slave start 。

如果主从断了，你还要从新拷贝数据，然后再导进去。