mysql主从配置实例_一个mysql主从复制的配置案例

环境说明

主库是111.111.111.110 从库为111.111.111.111

1. 主库创建数据库复制专用用户

--在主库上执行,创建用户repl

1 [mysqld]2

3 datadir=/var/lib/mysql4

5 socket=/var/lib/mysql/mysql.sock6

7 user=mysql8

9 log-bin=mysql-bin10

11 old_passwords=1

12

13 server_id = 110

14

15 sync_binlog = 1

16

17 relay_log = /var/lib/mysql/mysql-relay-bin18

19 # enable log_slave_updates20

21 log_slave_updates = 1

22

23 skip_slave_start24

25 [mysqld_safe]26

27 log-error=/var/log/mysqld.log28

29 pid-file=/var/run/mysqld/mysqld.pid

修改主库参数如下

vi /etc/my.cnf

1 [mysqld]2

3 datadir=/var/lib/mysql4

5 socket=/var/lib/mysql/mysql.sock6

7 user=mysql8

9 log-bin=mysql-bin10

11 old_passwords=1

12

13 server_id = 110

14

15 sync_binlog = 1

16

17 relay_log = /var/lib/mysql/mysql-relay-bin18

19 # enable log_slave_updates20

21 log_slave_updates = 1

22

23 skip_slave_start24

25 [mysqld_safe]26

27 log-error=/var/log/mysqld.log28

29 pid-file=/var/run/mysqld/mysqld.pid

2.关闭数据库

--在主库上执行

1 service mysqld stop

3.备份主库

--在主库上执行

1 tar zcvf /tmp/mysql.tar.gz /var/lib/mysql

4.将主库的备份传送到从库

--在主库上执行

rsync -auzvP --bwlimit=10240 /tmp/mysql.tar.gz 111.111.111.111: /tmp/mysql.tar.gz

rsync –auzvP --bwlimit=1024 /etc/my.cnf 111.111.111.111:/etc/my.cnf

5.在备库恢复主库的备份

--在备库执行

1 mv /var/lib/mysql /var/lib/mysqlbak2

3 mv /tmp/mysql.tar.gz /var/lib/

4

5 tax xvf /var/lib/mysql.tar.gz

6.在备库修改配置文件/etc/my.cnf

--在备库执行

--修改配置文件/etc/my.cnf

vi /etc/my.cnf

修改参数如下

1 [mysqld]2

3 server_id = 111

7.在备库启动数据库

--在备库执行

service mysqld start

8.在主库上启动数据库

1 service mysqld start2

3 mysql –uroot –p4

5 show master status;

9.根据上一步获取到的bin log文件名和position，在备库启用复制

--在备库执行(将命令master_log_file='mysql-bin.0000099',master_log_pos=5中的值mysql-bin.0000099和0 修改为上一步获取到的值)

1 change master to master_host='111.111.111.110',2

3 master_user='repl',4

5 master_password='repl',6

7 master_log_file='mysql-bin.00000xx',8

9 master_log_pos=0;

--启动复制进程

1 start slave;

2

3 show slave status\G

4

5 show processlist\G