mysql主从同步备份

说明：
本文主要讲mysql主从数据同步，从mysql实时同步主mysql的数据。这里，一般要求主msyql与从mysql版本相同或主msyql不高于从mysql版本(版本查询>select version()）。一般稳健的做法都是使其版本相同，因为不同mysql版本之间的binlog(二进制日志)格式可能不一样，有可能会导致同步异常。

主mysql: 192.168.1.202
从mysql: 192.168.1.203

实现：
一.配置主mysql
1.修改主mysql配置,注意这里主mysql与从mysql server-id一定不能相同

# vim /etc/mysql/my.cnf  //内容如下
server-id = 1
log-bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10     //设置binlog超期日期
max_binlog_size = 100M   //不一定要，设置单个binlog最大文件大小
binlog_do_db = myslave //需要同步多个数据库，请另起一行设置binlog_do_db = 同步数据库名称
binlog_ignore_db = mysql   //不同步数据库

 
2.重启主mysql,使配置生效

# /etc/init.d/mysql restart

 
3.主mysql中给予从mysql REPLICATION SLAVE权限即可，192.168.1.203为从库ip

> grant replication slave on *.* to slave@192.168.1.203 identified by '123456';
> quit

 
4.主mysql新建myslave数据库及表结构，用于测试。

# /usr/local/mysql/bin/mysql -u root -p -S /tmp/mysql.sock
> CREATE DATABASE myslave CHARSET 'utf8';
> use myslave;
> CREATE TABLE `users` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL,
`website` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

> CREATE TABLE `users_log` (
`user_log_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`info` varchar(200) DEFAULT NULL,
`uid` mediumint(8) unsigned NOT NULL,
PRIMARY KEY (`user_log_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

 
5.主mysql导出myslave数据库，先锁住myslave不让其有写入操作,这个窗口不要关，否则lock会失效

> flush tables with read lock;
# mysqldump -uroot -proot -P3306 --databases myslave > /tmp/backdb.sql  //导出单个数据库时
# mysqldump -uroot -proot -P3306 --databases db1 db2 > /tmp/backdb.sql  //导出多个数据库时
> unlock tables  //导出成功后，解锁表

 
6.新开个终端，进入mysql查看binlog的File，Position，记下，很重要！

# /usr/local/mysql/bin/mysql -u root -p -S /tmp/mysql.sock
> show master status;
+——————+———-+————–+——————+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000001 |      106 | myslave      | mysql            |
+——————+———-+————–+——————+

 
二.从mysql操作，修改my.cnf
1.修改从mysql配置文件/etc/my.cnf

# vim /etc/mysql/my.cnf
server-id       = 10  //我只修改了这条，其它都是在mysql命令行修改。所以下面的都不需要
master-host     = 192.168.45.7
master-user     = slave
master-password = 123456
master-port     = 3306
master-connect-retry    = 5
replicate-do-db = myslave
replicate-ignore-db     = mysql

 
2.修改完my.cnf重启mysql,使配置生效

# /etc/init.d/mysql restart

 
3.导入主mysql dump出的数据。可能需要先使用create database 库，新建库，具体没注意。

# /usr/local/mysql/bin/mysql -u root -p -S /tmp/mysql.sock
> source /tmp/backdb.sql  //个人理解，如果多个库在同一个mysqldump文件中，要同时导入所有库，就不用> use 库，直接source /tmp/backdb.sql。如果要导入多个库，但不是全部，可能得> use db1,> source /tmp/backdb.sql；> use db2,> source /tmp/backdb.sql

 
4.查看是否导入成功

> show tables;
+——————-+
| Tables_in_myslave |
+——————-+
| users             |
| users_log         |
+——————-+
2 rows in set (0.00 sec)

 
5.配置Slave信息并启动Slave服务，修改master前要先停slave服务

> stop slave  //默认是关闭
> CHANGE MASTER TO
> MASTER_HOST='192.168.1.100',
> MASTER_USER='slave',
> MASTER_PORT=3306,     //注意不要引号
> MASTER_PASSWORD='123456',
> MASTER_LOG_FILE='mysql-bin.000004',
> MASTER_LOG_POS=106;   //注意不要引号

以上关键的是MASTER_LOG_FILE以及MASTER_LOG_POS值，可以从Master服务器获得，如果不设置，将无法正常启动Slave服务

6.启动Slave服务

> start slave;
> show slave status/G;

当输入命令后会出现一大堆的状态，具体看Slave_IO_Running以及Slave_SQL_Running的状态，如果都是Yes就代表正常
然后还可以看到Slave_IO_State的状态是Waiting for master to send event，含义是等待Master服务器发送事件

三.测试:，在Master服务器插入一条数据

> use myslave;
> insert into users(username,website) values('edison','http://www.gaingreat.com');

插入成功之后，稍等片刻，就可以在Slave服务器上查看到相同记录，主从同步有时延

附录：
1.查看从mysql状态

>show slave statusG;
Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

以上二行同时为Yes说明配置成功

2.查看主mysql状态

>show master status;

 

3.如果想要查看当前Slave服务器的同步状态及偏移值等信息，请查找master.info文件

more /var/lib/mysql/master.info

 

4.查看Slave执行binlog文件的情况

#more /var/lib/mysql/relay-log.info

 

原文参考:
MySQL主从复制（Master-Slave）与读写分离（MySQL-Proxy）实践
MySQL数据库的同步配置+MySql 读写分离