ubuntu 11.04 mysql 主从同步

第一部分：环境说明

1、OS：UBUNTU 11.04（X64）
2、mysql版本：5.1.63
3、IP配置：master：192.168.1.57 slave：192.168.1.52

第二部分：配置过程
1、master#cat /et/mysql/my.cnf
[mysqld]
server-id = 1
log-bin=/var/log/mysql/mysql-bin
binlog_format=mixed (保持主从库的日志格式一致，如果不一致可能会不成功)

2、master#service mysqld restart                                                                              ##重起数据库服务
3、grant replication slave 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.54上
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、按照如下内容修改/etc/mysql/my.cnf文件
[mysqld]
log-bin=mysql-bin                                                                                             ##日志名称
binlog_format=mixed                                                                                           ##日志格式
server-id = 2                                                                                                 ##从服务器id
master-host = 192.168.1.57                                                                                    ##主服务器master地址
master-port = 3306                                                                                            ##主服务器mysql服务端口
master-user = slave                                                                                           ##同步服务器的账户
master-password = admin                                                                                       ##对应的密码
master-connect-retry = 60                                                                                     ##失败重连的时间间隔
replicate-ignore-db = mysql                                                                                   ##忽略的数据库

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状态