Mysql的主從伺服器
對於公司資料庫的需要,我們有時需要建立主從伺服器,在這裡我給大家演示兩個建立過程,一個是對於一個全新的主從伺服器的建立(即資料庫里還沒有存在任何數據信息),另一個是在有數據信息的基礎上建立從伺服器.
在演示之前,先簡單介紹一個主從伺服器的相關知識:
從伺服器
是如何實現通過主伺服器實現數據複製的:主伺服器通過dump線程將主伺服器上的二進位日誌傳遞給了從伺服器,從伺服器將此二進位日誌變成自己的中繼日誌,在通過I/O線程將中繼日誌傳遞給本機上的SQL線程,本機通過SQL線程寫入數據,來實現數據的複製.
注意事項:
每個伺服器都有唯一的service ID
在主伺服器上需要建立從伺服器複製數據用的賬號
若是在互聯網上傳輸需要利用ssl
作為從伺服器,其資料庫的版本不能比主伺服器低,以免一些SQL命令用到的功能不能在從伺服器上執行
案例一:全新的沒有任何數據
前提:兩台主機上都安裝好了
mysql,其能夠正常啟動,
在主伺服器上執行以下操作
[root@mail ~]# vim /etc/my.cnf
innodb_file_per_table=1 可添加如下一行使mysql的每個表有自己獨立的空間
[root@mail ~]# service mysqld start 啟動mysql
Starting MySQL..... [ OK ]
mysql> grant replication client,replication slave on *.* to repl@'172.16.%.%' identified by 'redhat'; 在主伺服器上添加從伺服器可以使用的本地用戶,使他對於所有資料庫的所有表有許可權,其用戶名是repl,主機地址是
172.16網段的所有地址,密碼是redhatmysql> flush master; 清空主伺服器日誌
在從伺服器上執行以下操作
[root@localhost ~]# vim /etc/my.cnf 修改mysql的配置文件信息
server-id = 11 不能和主伺服器的service ID一樣
#log-bin=mysql-bin 關閉二進位日誌,是否關閉可以根據自己的需求決定
relay-log=relay-bin 開啟了中繼日誌的功能
relay-log-index=relay-bin.index
innodb_file_per_table=1
[root@localhost ~]# service mysqld start 啟動mysql
mysql> flush slave; 清空從伺服器日誌
mysql>change master to master_host='172.16.19.9',master_user='repl',master_password
='redhat'; 指定其主伺服器是172.16.19.9上的資料庫,複製數據時使用的賬號和密碼
mysql> start slave; 開啟從伺服器
mysql> show slave status\G; 若這兩個運行狀態都為yes,則主從伺服器建立成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
可以驗證一下是否真正成功
mysql> create database feng; 在主伺服器上創建資料庫
Query OK, 1 row affected (0.00 sec)
mysql> show databases; 在從伺服器上查看
--------------------
| Database |
--------------------
| information_schema |
| feng |
| mysql |
| performance_schema |
| test |
--------------------
顯示出這個表中包含在主伺服器上創建的資料庫feng,則恭喜你全新的主從伺服器創建成功.
案例二:給已經有數據的資料庫建立從伺服器
在主伺服器上執行以下操作
mysql> grant replication client,replication slave on *.* to repl@'172.16.%.%' identified by 'redhat'; 在主伺服器上添加用戶
mysql> flush logs;
[root@mail ~]# mysqldump --all-databases --lock-all-tables --master-data=2 >/tmp/all-slave.sql 由於我的也是在虛擬機上新建的mysql, root用戶還沒有密碼,所有執行了這樣的命令,
若你的mysql已經有密碼,執行的命令則如下
# mysqldump -uroot -p'redhat' --all-databases --lock-all-tables --master-data=2 >/tmp/all-slave.sql 將數據保存到/tmp/all-slave.sql
mysqldump是資料庫備份命令,-u後跟用戶,-p後跟密碼
--all-databases 指定所有的資料庫
--lock-all-tables 並對所有的表上鎖
--master-data=2 在備份的時候導出二進位文件的位置
在從伺服器上執行以下操作
將在主伺服器上備份的文件all-slave.sql,移動到從伺服器的/tmp目錄下
[root@localhost ~]# vim /etc/my.cnf 修改以下信息
server-id = 11
#log-bin=mysql-bin
relay-log=relay-bin
relay-log-index=relay-bin.index
innodb_file_per_table=1
[root@localhost ~]# service mysqld start 啟動mysql
Starting MySQL.. [ OK ]
[root@localhost ~]# head -30 /tmp/
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=183;
查看文件all-slave.sql,記住如下行從哪個二進位日誌開始到哪個點
mysql> source /tmp/all-slave.sql; 載入一些備份的資料庫
mysql>change master to master_host='172.16.19.9',master_user='repl',master_password
='redhat',master_log_file='mysql-bin.000003',master_log_pos=183;
此處在指定主伺服器的時候還要指出從哪個日誌的哪個點開始
mysql> start slave;
mysql> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
mysql> show databases;
--------------------
| Database |
--------------------
| information_schema |
| extmail |
| feng |
| haha |
| jiaowu |
| jun |
| mydb |
| mysql |
| performance_schema |
| test |
--------------------
10 rows in set (0.00 sec)
通過資料庫可以看出備份的數據再入成功
可以驗證是否主從伺服器真正建立成功
在主服務上執行下面的命令
mysql> show slave hosts; 顯示有哪些從伺服器
----------- ------ ------ -----------
| Server_id | Host | Port | Master_id |
----------- ------ ------ -----------
| 11 | | 3306 | 1 |
----------- ------ ------ -----------
如果你能看到這個表格,同樣恭喜你,主從伺服器你也已經穿件成功了.
對於這樣創建主從伺服器,其實還存在一些隱患的,從伺服器可以寫入數據的,嚴格來說從伺服器是不允許寫入數據的,所以主從伺服器可以使用以下配置:
主伺服器:
sync_binlog=1 任何事務提交之後都要寫入二進位文件
innodb_flush_logs_at_trx_commit=1 在事務提交的時候將日誌立即寫入磁碟
這兩項來解決伺服器突然崩潰造成事務提交但沒有寫入二進位日誌的問題
從伺服器:
skip_slave_start=1 定義mysql啟動時不自動進入從伺服器線程
read_only=1
定義從服務沒有自己寫數據的許可權relay-log=relay-bin 開啟了中繼日誌的功能
relay-log-index=relay-bin.index
replicate_do_db=db_name 定義從伺服器對哪些資料庫進行複製
rpplicate_ignore_db=db_name 定義從伺服器不給哪些資料庫經行複製
replicate_do_table=tb_name 對錶的限制
replicate_ignore_table=tb_name
replicate_wild_do_table=mageedu.tb% 對那些表的限制
replicate_wild_ignore_table
若可以對多個資料庫複製則要使用一些格式
replicate_do_db=db_name1
replicate_do_db=db_name2
本文出自 「譕淚らづ寳唄」 博客,請務必保留此出處http://fengjunfei.blog.51cto.com/4356100/831969
[火星人 ] mysql主從伺服器的創建方法和需要注意的相關事項已經有601次圍觀