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
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; |
主从搭建一般步骤: