CentOS6.5搭建MySQL5.1主从复制

环境：

1 2 3 4 5 6 7

mysql> select version(); +------------+ | version()  | +------------+ | 5.1.73-log | +------------+ 1 row in set (0.00 sec)

　　

1 2	[root@10-4-14-168 ~]# cat /etc/redhat-release CentOS release 6.5 (Final)

　　

1：已经事先购买了两台云主机

10.4.14.168      101.69.178.208 [联通] Master

10.4.5.9         101.69.178.219 [联通] Slave 

2：分别安装MySQL

 

 
#yum install mysql-server –enablerepo=remi
 
#yum install mysql-devel

 

3：查看MySQL安装情况

 [root@10-4-14-168 ~]#  rpm -qa | grep mysql*

mysql-5.1.73-3.el6_5.x86_64
mysql-libs-5.1.73-3.el6_5.x86_64
mysql-server-5.1.73-3.el6_5.x86_64
mysql-devel-5.1.73-3.el6_5.x86_64

 

4：启动MySQL

 

service mysqld start

 

 

5：登陆MySQL并且修改密码并且删除空用户

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

mysql> UPDATE mysql.user SET password = PASSWORD('ge0513.mysql')     -> WHERE user = 'root'; Query OK, 3 rows affected (0.00 sec) Rows matched: 3  Changed: 3  Warnings: 0   mysql> SELECT user,host,password FROM mysql.user; +------+-------------+-------------------------------------------+ | user | host        | password                                  | +------+-------------+-------------------------------------------+ | root | localhost   | *7AE39BE5035D5C32361400FF7DEDD757AA76896A | | root | 10-4-14-168 | *7AE39BE5035D5C32361400FF7DEDD757AA76896A | | root | 127.0.0.1   | *7AE39BE5035D5C32361400FF7DEDD757AA76896A | |      | localhost   |                                           | |      | 10-4-14-168 |                                           | +------+-------------+-------------------------------------------+ 5 rows in set (0.00 sec) mysql> DROP user ''@localhost; Query OK, 0 rows affected (0.00 sec)   mysql> DROP user ''@'10-4-14-168'; Query OK, 0 rows affected (0.00 sec)

 

6 ：主库需改配置文件，在/etc/my.cnf中添加

1 2 3 4

port = 3306 log_bin = /var/lib/mysql/mysql-binlog server-id = 1 //只要主从不一样就行 binlog_do_db = test//要做同步的数据库名字，可以是多个数据库，之间用分号分割。

　　从库的配置文件中添加

1 2 3 4 5 6 7

server-id = 2 master-host = 10.4.14.168 master-user = gechong master-password = gechong master-port = 3306 master-connect-retry = 5 replicate-do-db = test

　　分别重启服务，登陆数据库。

 

7：主库上创建复制用户

1	GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO gechong@'%' IDENTIFIED BY 'gechong';

8：在主库上执行

1 2 3 4 5 6 7

mysql> show master status; +---------------+----------+--------------+------------------+ | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---------------+----------+--------------+------------------+ | binlog.000003 |      412 |              |                  | +---------------+----------+--------------+------------------+ 1 row in set (0.00 sec)

从库上执行

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41

mysql> show slave status\G *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 10.4.14.168                   Master_User: gechong                   Master_Port: 3306                 Connect_Retry: 5               Master_Log_File: mysql-binlog.000001           Read_Master_Log_Pos: 325                Relay_Log_File: mysqld-relay-bin.000002                 Relay_Log_Pos: 473         Relay_Master_Log_File: mysql-binlog.000001              Slave_IO_Running: Yes             Slave_SQL_Running: Yes               Replicate_Do_DB: test           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: 325               Relay_Log_Space: 629               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，说明配置成功了。可以在主库的test库里执行DML或者DDL验证下。

 

 

 

如果同步不成功：

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

1：停掉从库 mysql> slave stop 2：主库上找到日志和位置 mysql> show master status; 3：手动同步 mysql> change master to > master_host='master_ip', > master_user='gechong', > master_password='gechong', > master_port=3306, > master_log_file='mysql-bin.000020', > master_log_pos=135617781; 1 row in set (0.00 sec) 4：启动从库 mysql> slave start; 1 row in set (0.00 sec)

 

　　

 

 

如果有异常需要跳过：

1 2 3

>slave stop; >SET GLOBAL sql_slave_skip_counter = 1; >slave start;

　　

主从搭建一般步骤：