centos 6.6 上mysql5.1主從同步基礎實驗

標籤：主從同步；mysql   linux；   

注意本次主從伺服器上面均一yum安裝mysql
主從安裝mysql
shell> yum -y install mysql mysql-server

/etc/init.d/mysqld start #初始化資料庫，並啟動資料庫

主伺服器上面添加以下配置server-id=1log-bin=mysql-bin #這個一定得設定，否則沒有日誌的話，從資料庫上會報錯

[[email protected] etc]# service mysqld stop
Stopping mysqld: [ OK ]
[[email protected] etc]# service mysqld start
Starting mysqld: [ OK ]
[[email protected] etc]# service mysqld status
mysqld (pid 3129) is running...

建立主從複製的帳號：mysql> GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO [email protected]‘192.168.137.197‘ IDENTIFIED BY ‘123456‘;Query OK, 0 rows affected (0.00 sec)如果該命令無法執行，請檢查是否用了圓角的’，需要用半形的‘，再不行，那就是mysql資料庫安裝不成功。192.168.137.197是從庫的地址backup是需要同步的使用者名稱(有些部落格說是資料庫名稱，坑人不倦)123456為遠程同步密碼解鎖表

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

正確的狀態如下：mysql> SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 106 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

 配置錯誤的情況，解決辦法見文章最後的疑難解答。mysql> SHOW MASTER STATUS;Empty set (0.00 sec)修改從庫配置：

[[email protected] ~]# vi /etc/my.cnf
添加語句
log-bin = mysql-bin
server_id = 2
master-host = 192.168.137.33
master-user = backup
master-pass = 123456
master-port = 3306
master-connect-retry = 60

重啟mysql /etc/init.d/mysqld restart 

mysql> CHANGE MASTER TO
MASTER_HOST=‘192.168.137.33‘,
MASTER_USER=‘backup‘,
MASTER_PASSWORD=‘123456‘,
MASTER_PORT=3306,
MASTER_LOG_FILE=‘mysql-bin.000001‘,
MASTER_LOG_POS=106,
MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.02 sec)
測試主從是否配置成功。
mysql> show slave status\G;

#注意在slave上面檢測 show slave status時要保證 1、 Slave_IO_State 和Slave_IO_Running 都是有狀態的才算salve進程啟動，後續才能進行主從操作。切記！測試：建立表：CREATE TABLE Persons-> (-> Id_P int,-> LastName varchar(255),-> FirstName varchar(255),-> Address varchar(255),-> City varchar(255)-> );    mysql> desc Persons;

+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| Id_P | int(11) | YES | | NULL | |
| LastName | varchar(255) | YES | | NULL | |
| FirstName | varchar(255) | YES | | NULL | |
| Address | varchar(255) | YES | | NULL | |
| City | varchar(255) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

從伺服器上面mysql>desc Persons之後出現上述一樣的結果則說明配置，主從同步成功。但是這個只是主伺服器的檔案變動之後同步到從伺服器上面去了，從伺服器的sql語句執行，沒有影響到主伺服器的變化，並沒有實現真正意義上的主從同步。請知悉！

centos 6.6 上mysql5.1主從同步基礎實驗