Mysql配置主从复制

1.环境：
E3DDBA11:172.16.13.111 --Slave
E3DDBA12:172.16.13.112 --Master
Mysql版本均为mysql Ver 14.14 Distrib 5.1.61, for redhat-linux-gnu (x86_64) using readline 5.1

[wh42@e3ddba11 etc]$ uname -a
Linux e3ddba11 2.6.32-279.el6.x86_64 #1 SMP Fri Jun 22 12:19:21 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux
[wh42@e3ddba11 etc]$ cat /etc/redhat-release
CentOS release 6.3 (Final)

[wh42@e3ddba12 ~]$ uname -a
Linux e3ddba12 2.6.32-279.el6.x86_64 #1 SMP Fri Jun 22 12:19:21 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux
[wh42@e3ddba12 ~]$ sudo cat /etc/redhat-release
CentOS release 6.3 (Final)

2.分别在主从上创建用作复制的数据库。

mysql> create database wison;
Query OK, 1 row affected (0.01 sec)

3.master和slave机器上的配置修改
3.1 修改master机器上的mysql的配置文件my.cnf。
[wh42@e3ddba12 ~]$ sudo vi /etc/my.cnf
在mysqld下面添加
server-id=1---表明为master角色
log-bin=log---启用log
binlog-do-db=wison---表明需要做复制的数据库
binlog-ignore-db=mysql---表明需要忽略的数据库
在master机器上为slave机器添加复制的账号

mysql> grant replication slave on *.* to ‘repl‘@‘172.16.13.111‘ identified by ‘123456‘;
重启master机器上的mysql服务
[wh42@e3ddba12 ~]$ sudo service mysqld restart
服务重启之后，检查日志状况
mysql> show master status;
+------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------+----------+--------------+------------------+
| log.000001 | 106 | wison | mysql |
+------------+----------+--------------+------------------+
1 row in set (0.00 sec)

3.2在slave上mysql配置文件my.cnf
[wh42@e3ddba11 etc]$ sudo vi /etc/my.cnf
在mysqld下面添加如下内容
server-id=2---表明为slave
master-host=172.16.13.112---指向master机器的ip地址
master-user=repl---指明用什么账号实施replication
master-password=123456---指明同步账号的密码
master-port=3306---指明master的mysql端口号
master-connect-retry=60---如果slave发现master断掉，重新连接的时间
replicate-do-db=wison---表示复制的数据库
replicate-ignore-db=mysql---表示忽略的数据库
重启slave机器上的mysql服务
[wh42@e3ddba11 etc]$ sudo service mysqld restart
3.3在slave上进入mysql
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.13.112
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: log.000001
Read_Master_Log_Pos: 106
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 245
Relay_Master_Log_File: log.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: wison
Replicate_Ignore_DB: mysql
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: 401
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)

如果Slave_IO_Running和Slave_SQL_Running都返回YES，表示设置replication成功。

此时我们进行测试。
[wh42@e3ddba12 ~]$ mysql -uroot -pwison
mysql> use wison
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table abc (id int ,name varchar(10)) engine =myisam;
Query OK, 0 rows affected (0.07 sec)

mysql> insert into abc select 1,‘wison‘;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0

此时在slave上查看对应的表和数据已经存在
[wh42@e3ddba11 etc]$ mysql -uroot -p
Enter password:
mysql> use wison
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_wison |
+-----------------+
| abc |
| repltest |
| test |
+-----------------+
3 rows in set (0.00 sec)

mysql> select * from abc;
+------+-------+
| id | name |
+------+-------+
| 1 | wison |
+------+-------+
1 row in set (0.00 sec)

mysql>

Mysql配置主从复制,古老的榕树,5-wow.com