mysql-ab复制

yoomck

A--:192.168.0.200
B--:192.168.0.201
①  分别在主机A,B上面建立用于Replication的用户.给予REPLICATION SLAVE权限即可
②  shell>mysqld_safe -user=mysql&
A:
shell>mysql -uroot -ppassword
mysql>GRANT REPLICATION SLAVE ON *.*
  -> TO
[email=replicate@'192.168.0.201']replicate@'192.168.0.201'[/email]
identified by '123';
mysql>quit;
停止mysql Server.
B:
shell>mysql -uroot -ppassword
mysql>GRANT REPLICATION SLAVE ON *.*
  -> TO
[email=replicate@'192.168.0.200']replicate@'192.168.0.200'[/email]
identified by '123';
mysql>quit;
停止mysql Server.
③分别修改A,B主机的mysql 配置文件.均如下操作
shell>vi /etc/my.cnf
     ## A主机修改为
[mysqld]
log-bin
server-id=1
     ## B主机修改为
[mysqld]
log-bin
server-id=2
     ##保存退出
④ A 主机用如下方式启动mysql.
shell>mysqld_safe --skip-slave-start --user=mysql&
     ##登陆mysql
shell>mysql -uroot -ppassword
mysql>change master to
  ->MASTER_HOST='192.168.0.201',
  ->MASTER_USER='replicate',
  ->MASTER_PASSWOR='123';
  
mysql> start slave;
⑤ B主机也用如下方式启动mysql.
shell>mysqld_safe --skip-slave-start --user=mysql&
     ##登陆mysql
shell>mydql -uroot -ppassword
mysql>change master to
  ->MASTER_HOST='192.168.0.200',
  ->MASTER_USER='replicate',
  ->MASTER_PASSWORD='123';
mysql> start slave;
⑥现在已基本完成了操作,在任意一台运行如下命令查看master/slave状态
mysql> show master status;  ##如果Server-id没有设置,此处将会有警告信息
+----------------------+--------------+--------------+------------------+
| File         | Position   | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+--------------+--------------+------------------+
| localhost-bin.000003 | 98      |       |         |
+----------------------+--------------+--------------+------------------+
1 row in set (0.03 sec)   
****************************************
mysql> show slave status;
+----------------------------------+-------------+-------------+-------------+---------------+----------------------+---------------------+----------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Slave_IO_State          | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File   | Read_Master_Log_Pos | Relay_Log_File       | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |
+----------------------------------+-------------+-------------+-------------+---------------+----------------------+---------------------+----------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Waiting for master to send event | 192.168.0.201 | repl    | 3306    | 60      | localhost-bin.000003 | 98         | localhost-relay-bin.000008 | 239      | localhost-bin.000003 | Yes        | Yes        |         |           |          |            |             |               | 0     |      | 0      | 98         | 239       | None      |        | 0       | No         |          |          |         |          |        | 0           |
+----------------------------------+-------------+-------------+-------------+---------------+----------------------+---------------------+----------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
1 row in set (0.01 sec)
*****************************************
mysql> show processlist;
+---+-------------+-------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| Id| User    | Host       | db  | Command   | Time | State                                 | Info |
+---+-------------+-------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| 1 | system user |          | NULL | Connect   | 2073 | Waiting for master to send event                   | NULL       |
| 2 | system user |          | NULL | Connect   | 1426 | Has read all relay log; waiting for the slave I/O thread to update it | NULL       |
| 5 | repl    | 192.168.0.3:48356 | NULL | Binlog Dump | 832 | Has sent all binlog to slave; waiting for binlog to be updated    | NULL       |
| 6 | root    | localhost     | NULL | Query    | 0  | NULL                                 | show processlist |
+----+-------------+-------------------+------+-------------+------+----------------------------------------------------------------------+------------------+
4 rows in set (0.00 sec)
***此处应该有3个以上的id才正确.
****************************************************************************************************************************************


本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u2/70208/showart_1002385.html