mysql数据库主从同步配置教程--数据库同步

43523

背景： 最近有一个mysql数据库同步的需求，我用了mysql主从同步的方法来实现。下面把步骤记录一下。


环境和拓扑

操作系统:Centos6.6 X64

mysql版本：5.1.73

Master: 10.6.1.210
Slave:  10.6.1.211



需求: 实现Master上test库同步到Slave上,但是禁止同步该库下的AA表


1．配置Master上的my.cnf
#vim  /etc/my.cnf 添加内容到[mysqld]下，设定只同步test 数据库：
[mysqld]
log-bin=mysql-bin
binlog_format=mixed
binlog_do_db=test

server-id=1

2．配置Slave上的my.cnf
#vim  /etc/my.cnf
添加内容到[mysqld]下：
log-bin=mysql-bin
binlog_format=mixed
server-id=10
relay-log =relay-bin
log_slave_updates=1
replicate_ignore_table=AA(忽略同步某个表)

3．在Master中建立一个备份帐户：每个slave使用标准的MySQL用户名和密码连接Master。
进行复制操作的用户会授予REPLICATION   SLAVE 权限。

命令如下：
#建立一个帐户repluser，并且只能允许10.6.1.211这个主机来登陆，密码是123456。
mysql>grant  replication client,replication  slave  on  *.*   to 'repluser'@'10.6.1.211'    identified by  '123456';

QueryOK,0 rows affected(0.00sec)

mysql>flush privileges;
QueryOK,0 rows affected(0.00sec)


4．拷贝数据，保持数据库内数据一致，新安装可以忽略此步骤。

备份Master上的test库，然后复制到从服务器上.

#mysqldump -u root -p password123   test>/tmp/test.sql
将导出的数据库复制到从服务器上。
#scp /tmp/test.sql root@10.6.1.211:/tmp/

在Slave上导入新的test数据库。 登陆从后运行
#mysql -u root -p password123 test</tmp/test.sql

5．重启mysql服务，主从server均要重启。
#service mysql restart

6．查看Master数据库上的bin文件以及时间点. 登录Master服务器的mysql  后执行：
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000015 |     2474 | test         |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

此处，bin文件为mysql-bin.000015，节点为2474。

7．启动从服务器的中继日志，登陆从服务器的mysql 后执行以下命令，标红部分为刚才在主服务器上查询到的bin  文件以及节点信息：

mysql>change master to master_host='10.6.1.210',master_user='repluser',
master_password='123456',master_log_file='mysql-bin.000015',master_log_pos=2474,
master_connect_retry=5
QueryOK,0 rows affected(0.03sec)

#开启从服务器节点的复制进程，实现主从复制；
mysql>start slave;

#查看从服务器状态，主要关注IO线程和SQL  线程的开启状况：
mysql>show slave status \G
…


…

Slave_IO_Running:Yes            #IO thread  是否运行
Slave_SQL_Running:Yes         #SQL thread是否运行
…
…

8．查看主从服务器上的线程状态
主服务器:

mysql>show processlist \G
…
…
State: Has sent all binlog to slave; waiting for binlog to be updated
…
…

从服务器
mysql>show processlist \G
…
…
State: Has read all relay log; waiting for the slave I/O thread to update it
…
…
至此,mysql数据库主从同步复制配置完成.




验证
1.  在Master上的test库下新建一个test表.   插入记录，然后看看是否同步到了Slave上。

Master上建表

mysql> CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ;


Master上插入记录
mysql>insert into test(id,name) values(1,'steven');

mysql> select * from test;
+----+--------+
| id | name   |
+----+--------+
|  1 | steven |
+----+--------+
1 row in set (0.00 sec)


Slave上查看是否同步过去.


mysql> select * from test;
+----+--------+
| id | name   |
+----+--------+
|  1 | steven |
+----+--------+
1 row in set (0.00 sec)


2. 在Master上的test库下新建一个AA表.   插入记录，然后看看是否同步到了Slave上。
在Master上查询AA表.
mysql> select * from AA;
+----+--------+
| id | name   |
+----+--------+
|  1 | Angelababy |
+----+--------+
1 row in set (0.00 sec)





Slave上查询AA记录是空.

mysql> select * from AA;
Empty set (0.00 sec)