mysql主从同步配置

MySQL数据库没有增量备份的机制。当数据量太大的时候备份是一个很大的问题。还好MySQL数据库提供了一种主从备份的机制(也就是双机热备)

优点：适合数据量大的时候。大的互联网公司对于MySQL数据备份，都是采用热机备份。搭建多台数据库服务器，进行主从复制。

实现机制：对于一个MySQL服务器，一般有两个线程来负责复制和被复制。当开启复制之后：

• 作为主服务器Master，会把自己的每一次改动都记录到二进制日志 binlog 中。（从服务器会负责来读取这个log，然后在自己那里再执行一遍。）

• 作为从服务器Slave，会用master上的账号登陆到master上，读取master的binlog，写入到自己的中继日志 Relaylog，然后自己的SQL线程会负责读取这个中继日志，并执行一遍。 

在考虑双机热备时，需要注意，一般意义上的双机热备都会有一个切换过程，这个切换过程可能是一分钟左右。在切换过程中，服务是有可能短时间中断的。但是，当切换完成后，服务将正常恢复。因此，双机热备不是无缝、不中断的，但它能够保证在出现系统故障时，能够很快恢复正常的服务，业务不致受到影响。而如果没有双机热备，则一旦出现服务器故障，可能会出现几个小时的服务中断，对业务的影响就可能会造成很严重的损失。

 配置步骤

准备两台测试的虚拟机，如上安装mysql环境，并开启mysql服务

主master : 192.168.0.100

从slave  :   192.168.0.101

1、配置主库：

1）、授权给从数据库服务器

mysql>GRANT REPLICATION SLAVE ON *.* to 'backup'@'192.168.0.101' identified by '123456';
mysql>FLUSH PRIVILEGES;

2）、修改主库配置文件，开启binlog，并设置server-id，每次修改配置文件后都要重启mysql服务才会生效

vim /etc/my.cnf

在该配置文件[mysqld]下面添加下面内容：

[mysqld]
log-bin=/var/lib/mysql/binlog
server-id=1
binlog-do-db = master_test

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
......

server-id：master端的ID号；

log-bin：同步的日志路径及文件名，一定注意这个目录要是mysql有权限写入的（我这里是偷懒了，直接放在了下面那个datadir下面）；

binlog-do-db：要同步的数据库名

还可以显示 设置不同步的数据库：

binlog-ignore-db = mysql 不同步mysql库和test库
binlog-ignore-db = test

修改配置文件后，重启服务：service mysqld restart

如果启动失败，通过cat /var/log/mysqld.log | tail -30  查看mysql启动失败的日志，从日志内容寻找解决方案。

3）、查看主服务器当前二进制日志名和偏移量，这个操作的目的是为了在从数据库启动后，从这个点开始进行数据的恢复

mysql> show master status;
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| mysql-bin.000013 |     107 | cmdb         |                  |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)

主服务器已配置好。

 

2、配置从库

1）、理所当然也是从配置文件着手，在/etc/my.cnf 添加下面配置：

[mysqld]
server-id=2
master-host=192.168.0.100
master-user=rep1
master-password=123456
master-port=3306
replicate-do-db=master_test
......

重启时报错：mysqld: unknown variable ‘master-host=

说明mysql不认识这些变量，网上搜罗了一番，原因是mysql5.5+版本主从复制不支持这些变量，需要在从库上用命令来设置：

mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.100',
　　MASTER_PORT=3306,
　　MASTER_USER='backup',
　　MASTER_PASSWORD='123456',
　　MASTER_LOG_FILE='mysql-bin.000013',
　　MASTER_LOG_POS=107; #后面两个参数的值与主库保持一致

2）、启动slave进程

mysql> slave start;
Query OK, 0 rows affected (0.04 sec)

3）、查看slave的状态，如果下面两项值为YES，则表示配置正确：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

从库正在等待主库更新数据。。。Waitin for master to send event...

 

三、同步主库已有数据到从库

主库操作：

1、停止主库的数据更新操作

mysql>flush tables with read lock;

2、新开终端，生成主数据库的备份（导出数据库）

[root@zhoujietest ~]# mysqldump -uroot -p123 master_test > master_test.sql

3、将备份文件传到从库

[root@zhoujietest ~]# scp master_test.sql root@192.168.0.101:/root/

4、主库解锁

mysql>unlock tables;

 

从库操作：

1、停止从库slave

mysql>slave stop;

2、新建数据库cmdb

mysql> create database master_test default charset utf8;

3、导入数据

[root@ops-dev ~]# mysql -uroot -p123 master_test<master_test.sql

4、查看从库已有该数据库和数据 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| master_test        |
| mysql              |
| performance_schema |
| test               |
+--------------------+

 

此时主从库的数据完全一致，如果对主库进行增删改操作，从库会自动同步进行操作。