导航

mysql主从复制

Posted on 2017-07-29 23:22  Simon1020  阅读(170)  评论(0编辑  收藏  举报

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

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

2. 

修改主数据库的配置文件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

3. 

在主服务器上, 创建一个充许从数据库来访问的用户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

4. 

好了,现在可以停止主数据的的更新操作,并生成主数据库的备份,我们可以通过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)

5.

之后是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语句,为了清晰了解主从复制的过程,我们不采用这个参数。

 

6.

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

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

 

 

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

1.

接着修改从数据库的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

3.

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

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文件,重新启动服务。

4.

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

 

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

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=MyISAM DEFAULT CHARSET=utf8