mysql 5.5.* 下的主从模式（转）

转自
http://blog.sina.com.cn/s/blog_4b75b0020100lpwe.html

replicate-ignore-db?

主服务器:CentOS 5.4 Mysql 5.5.3 -m3 源码 IP:192.168.1.22
从服务器:CentOS 5.4 Mysql 5.5.3 -m3 源码 IP:192.168.1.33

配置:
一、主服务器
1.1、创建一个复制用户，具有replication slave 权限。
mysql>grant replication slave on *.* to ‘slave001’@’192.168.1.22′ identified by ‘slave001’;
1.2、编辑my.cnf文件
vi /data0/mysql/3306/my.cnf
添加
server-id=1
并开启log-bin二进制日志文件
log-bin=mysql-bin
1.3、启动mysql数据库
/data0/mysql/3306/mysql start
1.4、设置读锁
mysql>[color=red]flush tables with read lock;[/color]
1.5、得到binlog日志文件名和偏移量
mysql>[color=red]show master status; [/color]
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.0000010 | 106| | |
+——————+———-+————–+——————+
1.6、备份要同步的数据库
[color=red]mysqldump test1 > test1.sql [/color]
或者
[color=red]/usr/local/mysql/3306/bin/mysqldump -S/usr/local/mysql/3306/mysqld3306.sock -uusername -ppsw --add-drop-table --add-locks --default-character-set=utf8 --hex-blob -R --opt --all-databases > allDBdump.sql[/color]
=====================================================================
常见选项：
--add-drop-table：添加DROP TABLE语句
--add-locks：用LOCK TABLES 和 UNLOCK TABLES 语句封装插入每个表格的语句，能加速数据导入速度
--all-databases, -A： 备份所有数据库
--compress, -C：用于在两个server之间复制时压缩数据
--databases, -B： 用于备份多个数据库，如果没有该选项，mysqldump 把第一个名字参数作为数据库名，后面的作为表名。使用该选项，mysqldum把每个名字都当作为数据库名。CREATE DATABASE IF NOT EXISTS db_name and USE db_name 会被添加到导出文件里。
--force, -f：即使发现sql错误，仍然继续备份
--host=host_name, -h host_name：备份主机名，默认为localhost
--no-data, -d：只导出表结构
--opt：等同于激活--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset这些选项
--password[=password], -p[password]：密码
--port=port_num, -P port_num：制定TCP/IP连接时的端口号
--protocol={TCP | SOCKET | PIPE | MEMORY}：制定连接所用的协议
--quick, -q：快速导出
--socket=path, -S path：当连接至localhost时，制定socket文件
--tables：覆盖 --databases or -B选项，后面所跟参数被视作表名
--user=user_name, -u user_name：用户名
--xml, -X：导出为xml文件
=====================================================================
1.7、解锁
mysql>[color=red]unlock tables; [/color]
二、从服务器
2.1、编辑my.cnf文件
vi /data0/mysql/3306/my.cnf
添加
server-id=2
注:需要把默认的server-id=1去掉
不要尝试把master配置属性写在my.cnf 中，5.1.7以后，mysql已经不支持这样做了
2.2、启动从数据库
/data0/mysql/3306/mysql start
导入数据库
[color=red]mysql -u用户名 -p 数据库名 < 数据库名.sql[/color]
或
[color=red]mysql -u用户名 -p < ***.sql (导入所有数据库)[/color]

2.3、对从数据库进行相应设置
mysql> [color=red]change master to
-> master_host='192.168.1.22',
-> master_port=3306,
-> master_user='slave001',
-> master_password='slave001',
-> master_log_file='mysql-bin.0000010',
-> master_log_pos=106,
-> replicate_wild_ignore_table=performance_schema.%,
-> replicate_wild_ignore_table=mysql.%,
-> replicate_wild_ignore_table=information_schema.%,
-> replicate_wild_ignore_table=test.% [/color]
2.4、启动从服务器slave线程
mysql>[color=red]start slave; [/color]
执行show processlist命令显示以下进程:
mysql>[color=red]show processlist\G [/color]

*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 2579
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL表示slave已经连接上master，开始接受并执行日志
2.5、查看slave线程状态
mysql>[color=red]show slave status; [/color]
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.22
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.0000010
Read_Master_Log_Pos: 106
Relay_Log_File: centos-relay-bin.000002
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.0000010
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 106
Relay_Log_Space: 830
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
验证是否配置正确
在从服务器上执行
mysql> [color=red]show slave status\G[/color]
Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如以上二行同时为Yes 说明配置成功
PS：show slave status\G 后不要添加 ； 号， 不然会出 ERROR
测试：
1、在主服务器test数据库中创建user表
mysql>use test1;
mysql>create table user(id int);
2、在从服务器中查看user表
mysql>use test1;
mysql> show tables like ‘user’;
+———————-+
| Tables_in_test(user) |
+———————-+
| user |
+———————-+
1 row in set (0.00 sec)
说明主从数据同步成功。
常见问题归纳：
1.在从数据库中查看slave状态时出现
The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the –replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it)
说明方从服务器里my.cnf中的server-id有相同的。
解决办法:
修改my.cnf里的server-id，并重启数据库服务。my.cnf文件默认有server-id=1
其它说明
主服务器my.cnf
[color=red]#binlog-do-db=需要备份的数据库名，可写多行
#binlog-ignore-db=不需要备份的数据库名，可写多行[/color]
从服务器my.cnf
配置已经无用，要在mysql中执行

三、手动更新
在主服务器上执行
mysql>flush tables with read lock;
Query OK,rows affected (0.01 sec)
mysql>show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.0000011 | 260| | |
+——————+———-+————–+——————
在从服务器上执行
mysql>select master_pos_wait(‘mysql-bin.0000011′,’260′);
+————————————————–+
| master_pos_wait(‘mysql-bin.0000011′,’260′) |
+————————————————–+
| 0 |
+————————————————–+
1 row in set (0.01 sec)
注意，执行这条语句的时候，可能会有MySQL server has gone away 错误，木有关系，多执行几次就好了

同步完成后，在主服务器上执行解锁
mysql>unlock tables;

[color=blue]一旦同步出错，有以下解决方法：[/color]
一是在my.cnf里加
slave-skip-errors=1062
1062为错误号

二是在备机跳过执行一条语句:set global sql_slave_skip_counter=1
1可以为N.
用来跳过备机的一条或N条出错的复制语句。然后重新start slave即可