mysql配置主從資料庫

標籤：date   lan   mysqldump   span   art   ble   參考資料   資料   diy   

1.目的

　　1.1 實現資料備份

　　1.2 項目訪問時可以實現讀寫分離，提高訪問和操作資料的速度<讀寫分離好處>

 

2.背景

　　這次的主從配置主要實現主庫資料的改變可以實現同步到從庫中；

　　此次實驗的主庫為liunx<ubuntu4.8.2> 下mysql 5.6.19，從庫為windows10 下mysql 5.6.24；

 

 3.準備知識

　　3.1 在liunx下mysql的設定檔為：

　　/etc/mysql/my.conf

 

　　3.2 在windows下mysql的設定檔為：

　　mysqlpath/my.ini

 

 　  3.3 在不同的系統內容下，配置的檔案的路徑和尾碼名是不同的；

　　3.4 在liunx系統內容下，mysql 服務的啟動關閉重啟的命令如下：

　　sudo start mysql; //啟動服務　　sudo stop mysql; //關閉服務　　sudo restart mysql; //重啟

 

 

 　　3.5 每次修改設定檔，需要重啟服務再查看結果

 

4. 配置主從資料庫

　　4.1 開啟3306連接埠，可以允許遠端存取主庫連接埠；

　　　　a.首先查看連接埠是否開啟

　　　　netstat -an|grep 3306

 

　　　　是開啟的情況下的資料展示，如果沒有開啟會出現 127.0.0.1 的資料；如果沒有開啟，則需要到 /etc/mysql/my.conf 中將

　　#bind-address = 127.0.0.1 //登出樣本　　bind-address = 127.0.0.1

 

　　　　重啟服務查看連接埠是否開啟；

 

　　　　b.設定遠程可以訪問mysql資料庫的許可權

　　　　　 進入主庫使用sql命令設定許可權，如下：　　　　

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || cmdb               || mysql              || performance_schema || test               |+--------------------+rows in set (0.00 sec)mysql> use mysql;Database changedmysql> update user set host=‘%‘ where user=‘root‘;ERROR 1062 (23000): Duplicate entry ‘%-root‘ for key ‘PRIMARY‘ //雖然報錯，但實際更新成功；

// 備選 mysql>grant all privileges on *.* to ‘root‘@‘%‘ identified by ‘xxxxxx‘;

　　　　

　　　　開啟另一個終端，執行如下命令：

[[email protected] ~]#mysql -u rootmysql>delete from user where user=‘‘;mysql>flush privileges; #重新整理許可權mysql>\q

 

　　　　重啟服務，執行如下命令查看許可權賦予情況：

mysql>mysql -u root -p 

mysql>use mysql; 

mysql>selsct user,host from user;

 

　　　　可得到如下資料：

　　　　host中%表示可以遠程使用root帳號訪問；

 

　　4.2 配置mysql主從設定檔

　　　　主庫：192.168.0.175

　　　　從庫：192.168.0.170

　　　　a.主庫配置 </etc/mysql/my.conf>

　　　　*添加的配置必須在[mysqld]範圍內，不然無法啟動服務
　　　　[mysqld]　　　　log-bin=mysql-bin　　　　server-id=1　　　　binlog-do-db = cmdb //需要同步的資料庫　　　　#binlog-ignore-db = nodb //不需要同步的資料庫

 

　　　　修改後重啟服務，查看主庫設定情況，如下：

mysql> show master status;+---------------+----------+--------------+------------------+| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |+---------------+----------+--------------+------------------+| binlog.000001 |     1304 | cmdb         |                  |+---------------+----------+--------------+------------------+row in set (0.00 sec)

 

　　　　b.從庫配置i<mysqlpath/my.ini>

　　　　設定設定檔：　　

[mysqld]server-id=2 //伺服器id，區別於主庫的server-idmaster-host=192.168.0.175 //主庫ipmaster-user=rep1 //5.5-master-password=test123456 //5.5-master-port=3306 //5.5-replicate-do-db=cmdb //5.5-......

 

　　　　設定檔除server-id外，mysql 5.5+ 版本需要在命令列中設定其餘屬性：

mysql> CHANGE MASTER TO    ->     MASTER_HOST=‘master_host_name‘,    ->     MASTER_USER=‘replication_user_name‘,    ->     MASTER_PASSWORD=‘replication_password‘,    ->     MASTER_LOG_FILE=‘recorded_log_file_name‘,//資料需要同show master status 中的資料一致    ->     MASTER_LOG_POS=recorded_log_position;//資料需要同show master status 中的資料一致

 

　　　　啟動slave進程

mysql> start slave;Query OK, 0 rows affected (0.04 sec)

　　

　　　　查看slave的狀態，如果下面兩項值為YES，則表示配置正確：

　　　　Slave_IO_Running: Yes　　　　Slave_SQL_Running: Yes

 

 

 

　　　　c.將主庫已有資料複製到從庫

　　　　　　ca.命令複製

　　　　　　主庫：　　　　

　　　　　　mysql>flush tables with read lock;//停止主庫的資料更新操作

 

　　　　　　開啟新的終端：

　　　　[[email protected] ~]# mysqldump -uroot -ptest123 cmdb > cmdb.sql //產生主要資料庫的備份（匯出資料庫）　　　　[[email protected] ~]# scp cmdb.sql root@192.168.0.170:/root/ //將備份檔案傳到從庫　　　　mysql>unlock tables;//主庫解鎖

 　　　　　

　　　　　　從庫：

mysql>slave stop;//停止從庫slavemysql> create database cmdb default charset utf8;//建立資料庫cmdb[[email protected] ~]# mysql -uroot -ptest123 cmdb<cmdb.sql //匯入資料mysql> show databases;//查看從庫已有該資料庫和資料 +--------------------+| Database           |+--------------------+| information_schema || cmdb               || mysql              || performance_schema || test               |+--------------------+

 

　　　　　　cb.使用工具<Navicat>直接從主庫複製到從庫中

 

　　　　d.主庫資料更改後，從庫自動更新。

 

 

 5. 後續問題

　　從庫修改資料後，會導致從庫同步主庫出錯，無法同步到主庫的資料；

　　解決方案：重新設定從庫資料，重新匯入資料，重啟服務。

 

6.項目配置讀寫分離

　　移步：http://www.cnblogs.com/springlight/p/5905720.html

 

參考資料：

　　1.http://www.cnblogs.com/zhoujie/p/mysql1.html

　　2.http://dev.mysql.com/doc/refman/5.6/en/replication-howto.html

　　3.http://www.linuxdiyf.com/linux/15206.html

　　4.http://www.cnblogs.com/linjiqin/p/5270938.html

 

能力有限，文檔可能存在不當之處，各位指正。

mysql配置主從資料庫