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：已經(jīng)事先購(gòu)買了兩臺(tái)云主機(jī)
10.4.14.168 101.69.178.208 [聯(lián)通] Master
10.4.5.9 101.69.178.219 [聯(lián)通] Slave
2：分別安裝MySQL
3：查看MySQL安裝情況
[root@10-4-14-168 ~]# rpm -qa | grep mysql*
4：?jiǎn)?dòng)MySQL
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: 0mysql> 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 ：主庫(kù)需改配置文件，在/etc/my.cnf中添加
1 2 3 4 | port = 3306log_bin = /var/lib/mysql/mysql-binlogserver-id = 1 //只要主從不一樣就行binlog_do_db = test//要做同步的數(shù)據(jù)庫(kù)名字，可以是多個(gè)數(shù)據(jù)庫(kù)，之間用分號(hào)分割。 |
從庫(kù)的配置文件中添加
1 2 3 4 5 6 7 | server-id = 2 master-host = 10.4.14.168 master-user = gechongmaster-password = gechongmaster-port = 3306master-connect-retry = 5replicate-do-db = test |
分別重啟服務(wù)，登陸數(shù)據(jù)庫(kù)。
7：主庫(kù)上創(chuàng)建復(fù)制用戶
1 | GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO gechong@'%' IDENTIFIED BY 'gechong'; |
8：在主庫(kù)上執(zhí)行
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) |
從庫(kù)上執(zhí)行
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: 0Master_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兩個(gè)值都是YES，說明配置成功了?？梢栽谥鲙?kù)的test庫(kù)里執(zhí)行DML或者DDL驗(yàn)證下。
如果同步不成功：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 1：停掉從庫(kù)mysql> slave stop2：主庫(kù)上找到日志和位置mysql> show master status;3：手動(dòng)同步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：?jiǎn)?dòng)從庫(kù)mysql> slave start;1 row in set (0.00 sec) |
如果有異常需要跳過：
1 2 3 | >slave stop;>SET GLOBAL sql_slave_skip_counter = 1;>slave start; |
主從搭建一般步驟：
聯(lián)系客服
