mysql 5.4.x_redhat 5.4 x64 mysql 主从同步

第一部分：环境说明

1、OS：Red Hat Enterprise Linux Server release 5.4 (Tikanga)

2、mysql版本：Server version: 5.0.77-log Source distribution

3、IP配置：master：192.168.1.50 slave：192.168.1.133

第二部分：配置过程

1、master#cat /et/mysql/my.cnf

[mysqld]

server-id = 1

log-bin = binlog

2、master#service mysqld restart                                                                              ##重起数据库服务

3、grant all on *.* to slave@… identified by 'admin';                                ##创建slave账户并设置密码、赋权限

4、mysql> flush tables with read lock;                                                                        ##锁表停止更新，不要退出这个终端，否则锁失效;

5、mysql> show master status;                                                                                 ##记录前两框的数据

+------------------------+------------+-----------------+----------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------------+------------+-----------------+----------------------+

| mysql-bin.000003 | 522 | | |

+------------------------+------------+-----------------+----------------------+

1 row in set (0.00 sec)

6、master#cd /var/lib/                                                                                        ##进入mysql目录的上级目录

tar czvf mysql.tgz mysql/                                                                                    ##对mysql目录进行打包

7、master#scp mysql.tgz root@…                                                      ##打包文件复制到1921.68.1.133上

8、slave# service mysql stop                                                                                  ##在slave上进行操作，停止mysql

slave#cd /var/lib                                                                                            ##进入/var/lib目录

slave#rm -rf mysql                                                                                           ##删除 mysql目录

9、slave#tar xzvf mysql.tgz                                                                                   ##解压缩mysql.tgz文件

10、slave#vi /etc/selinux/config,                                            ##修改selinux配置文件，内容改为如下

SELINUX=enforcing                                                         ##修改完毕，重起机器

11、按照如下内容修改/etc/mysql/my.cnf文件

[mysqld]

server-id = 2

replicate-ignore-db = mysql     不复制的库

replicate-ignore-db =  test       不复制的库                                ##切勿增加其他内容，例如master-host之类的参数会导致系统无法启动

11、slave#service mysql start                                                                                 ##启动数据库

12、mysql>slave stop;                                                                                         ##停止同步

13、手工同步数据库

mysql>change master to master_host='192.168.1.57',

master_user='slave',

master_password='admin',

master_port=3306,

master_log_file='mysql-bin.000003',                                                                           ##第五步中file列的值

master_log_pos=522,                                                                                           ##第五步种Position列的值

master_connect_retry=60;

14、mysql>slave start;                                                                                        ##开启同步

5、mysql>show slave status \G;                                                                                ##检查同步状态是否正常，Slave_IO_Running、Slave_SQL_Running值应为 “Yes”。

16、mysql>show processlist \G;                                                                                ##查看复制的进度

17、mysql>unlock tables;                                                                                      ##解除锁表，在master上执行

18、mysql> show master status;                                                                                ##查看master状态

19、mysql> show slave status \G;                                                                              ##查看slave状态

备注：Slave_IO_Running  = NO  往往是由于权限不足导致的