mysql主从复制

1.首先确保主从服务器上的Mysql版本相同

主: ==============================================================================================

修改主数据库的配置文件my.cnf(windows为my.ini),开启BINLOG，并设置server-id的值，修改之后必须重启Mysql服务

[mysql]

default-character-set=utf8

[mysqld]

server-id=134

lower_case_table_names=1
#binlog-do-db=db1
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=information_schema

#每表一文件
innodb_file_per_table=1
#启用二进制日志
log-bin=mysql-bin
log-bin-index=mysql-bin.index

#志滚动方式
#binlog_format=mixed
binlog_format=row

sync-binlog=1

在主服务器上, 创建一个充许从数据库来访问的用户slave,密码为：123456, 然后使用REPLICATION SLAVE赋予权限,如:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.200.138' IDENTIFIED BY '123456';

GRANT FILE ON *.* TO 'slave'@'192.168.200.138' IDENTIFIED BY '123456';

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to 'slave'@'192.168.200.138' identified by '123456';

flush privileges;

重启master机的mysql服务：service mysqld restart

好了，现在可以停止主数据的的更新操作，并生成主数据库的备份，我们可以通过mysqldump到处数据到从数据库，当然了，你也可以直接用cp命令将数据文件复制到从数据库去

注意在导出数据之前先对主数据库进行READ LOCK，以保证数据的一致性

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.19 sec)

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

mysql> show master status;

*************************** 1. row ***************************

File: mysql-bin.000003 ------二进制日志名

Position: 243 　　 -------偏移量

Binlog_Do_DB: 　　-------要进行主从复制的数据库

Binlog_Ignore_DB: -------不进行主从复制的数据库

1 row in set (0.00 sec)

之后是mysqldump

――备份数据库
对于事务性引擎（如：InnoDB），可使用--single-transaction参数获取一个一致性快照。
# mysqldump -h127.0.0.1 -P3306 -uroot -proot --default-character-set=utf8 --database happysunday -e -l --flush-logs --max_allowed_packet=1048576 --net_buffer_length=16384>/developer/happysunsay.sql

对于MyISAM引擎，或多引擎混合的数据库，可使用-l参数锁定表，获取一致性备份
# mysqldump -h127.0.0.1 -P3306 -uroot -proot --all-database -e -l --flush-logs --max_allowed_packet=1048576 --net_buffer_length=16384>/data/all_db.sql

注：因为我们提前使用flush tables with read lock命令给所有表加了读锁，所以此处也不必使用-l参数再加锁。

最好在主数据库备份完毕，恢复写操作

mysql> unlock tables;

Query OK, 0 rows affected (0.28 sec)

其实mysqldump还提供了一个--master-data参数，备份文件中包含了change master to语句，为了清晰了解主从复制的过程，我们不采用这个参数。

将刚才主数据备份的test.sql复制到从数据库，进行导入

――备份完成后，将备份文件拷贝到Slave端
# scp all_db.sql root@192.168.3.108:/data/

从: ==============================================================================================

接着修改从数据库的my.cnf(windows为my.ini),增加server-id参数,指定复制使用的用户,主数据库服务器的ip,端口以及开始执行复制日志的文件和位置

[mysql]

default-character-set=utf8

[mysqld]

server-id=138

innodb_file_per_table=1
#log-bin=mysql-bin
log-bin=/data/mysql/mysql-bin
binlog_format=row
log-slave-updates=1

#设置中继日志
relay-log=mysql-relay-log
#中继日志索引
relay-log-index=mysql-relay-log.index

read-only=1
slave_net_timeout=10
#需要同步的数据库
#replicate-do-db=test
replicate-wild-ignore-table=mysql.%

master-host=192.168.200.137
master-user=slave
master-pass=123456
master-port=3306
master-connect-retry=60

#sync_master_info=1
#sync_relay_log=1
#sync_relay_log_info=1
#master-info-repository=TABLE
#relay-log-info-repository=TABLE

2、

恢复slave
将Master库的备份恢复到Slave库，方法比较简单，直接执行即可。
# mysql -uroot -proot123 </data/all_db.sql

说明：skip-slave-start作用是启动从库但不立即启动从库的复制进程，以便对从库进行进一步的设置。

service mysql restart

在从服务器上开启复制功能, 配置从服务器

mysql>slave stop;

mysql>reset slave;

mysql>change master to master_host='192.168.200.137',master_port=3306,
master_user='slave',
master_password='123456',
master_log_file='mysql-bin.000001',
master_log_pos=106;

注意语句中间不要断开，master_port为mysql服务器端口号(无引号)，master_user为执行同步操作的数据库账户，“120”无单引号(此处的120就是show master status 中看到的position的值，这里的mysql-bin.000001就是file对应的值)。

4.在从服务器上,启动slave进程

mysql> start slave;

执行show processlist命令显示以下进程:
mysql> show processlist G

查看slave线程状态
mysql> show slave status G

说明：
Slave_IO_Running――此进程负责从Slave从Master上读取binlog日志，并写入Slave上的中继日志。
Slave_SQL_Running――此进程负责读取并执行中继日志中的binlog日志。
这两个进程的状态需全部为YES，只要有一个为NO，则复制就会停止。
当Slave中Relay_Master_Log_File和Master_Log_File相同且Read_Master_Log_Pos和Exec_Master_Log_Pos完全相同时，则表明Slave和Master处于完全同步的状态。

注：Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)

错误处理：

如果出现此错误：

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

因为是mysql是克隆的系统所以mysql的uuid是一样的，所以需要修改。

解决方法：

删除/var/lib/mysql/auto.cnf文件，重新启动服务。

好了,现在可以在我们的主服务器做一些更新的操作,然后在从服务器查看是否已经更新

CREATE TABLE `user` (
`id` bigint(20) NOT NULL,
`name` varchar(20) DEFAULT NULL,
`birthday` date DEFAULT NULL,
`update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`create` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8