Mysql數(shù)據(jù)庫(kù)安裝及生產(chǎn)環(huán)境下主從庫(kù)同步配置

1、Mysql數(shù)據(jù)庫(kù)安裝及生產(chǎn)環(huán)境下主從庫(kù)同步配置目 錄1安裝Mysql數(shù)據(jù)庫(kù)22生產(chǎn)環(huán)境下MySQL數(shù)據(jù)庫(kù)主從同步配置52.1 主數(shù)據(jù)庫(kù)配置 .52.2 從數(shù)據(jù)庫(kù)配置 53 監(jiān)控服務(wù)器63.1 監(jiān)控主數(shù)據(jù)庫(kù)服務(wù)器.63.2 監(jiān)控從數(shù)據(jù)庫(kù)服務(wù)器 .61 安裝Mysql數(shù)據(jù)庫(kù)安裝環(huán)境：系統(tǒng)： CentOS-6.6-x86_64數(shù)據(jù)庫(kù)： MySQL-server-5.5.42-1.el6.x86_64 ；MySQL-client-5.5.42-1.el6.x86_641. SSH方式登錄到MySQL服務(wù)器2. 創(chuàng)建存放安裝文件的目錄rootlocalhost /# mkdir -p /sw/mysql

2、553. 上傳安裝文件到上一步創(chuàng)建的目錄4. 檢查是否已安裝過(guò)MySQLrootlocalhost /# rpm -qa | grep -i mysqlMySQL-client-5.5.42-1.el6.x86_64MySQL-server-5.5.42-1.el6.x86_645. 如果已安裝則移除，否則請(qǐng)?zhí)^(guò)此步rootlocalhost /# yum -y remove MySQL-server-5.5.42-1.el6.x86_64rootlocalhost /# yum -y remove MySQL-client-5.5.42-1.el6.x86_64刪除老版本mysql的開(kāi)發(fā)頭文

3、件和庫(kù)rm -fr /usr/lib/mysqlrm -fr /usr/include/mysqlrm -fr /var/lib/mysqlrm -f /etc/f6. 安裝MySQLrootlocalhost /# cd /sw/mysql55/rootlocalhost mysql55# rpm -ivh MySQL-server-5.5.42-1.el6.x86_64.rpmPreparing. # 100% 1:MySQL-client # 100%rootlocalhost mysql55# rpm -ivh MySQL-client-5.5.42-1.el6.x86_64.rpmP

4、reparing. # 100% 1:MySQL-server # 100%7. 配置MySQLrootlocalhost mysql55# cp /usr/share/mysql/my-f /etc/frootlocalhost mysql55# vi /etc/fclient#password= your_passwordport= 8819socket= /var/lib/mysql/mysql.sockdefault-character-set=utf8mysqldport= 8819socket= /var/lib/mysql/mysql.socklower_case_table_n

5、ames=1skip-external-lockingkey_buffer_size = 384Mmax_allowed_packet = 8Mtable_open_cache = 512sort_buffer_size = 2Mread_buffer_size = 2Mread_rnd_buffer_size = 8Mmyisam_sort_buffer_size = 64Mthread_cache_size = 8Mquery_cache_size = 32M# Try number of CPUs*2 for thread_concurrencythread_concurrency =

6、16max_connections=10000character-set-server=utf8wait_timeout=31536000interactive_timeout=31536000user = mysqldefault_storage_engine = InnoDBpid-file = /var/lib/mysql/mysql.pid datadir = /var/lib/mysql/log_bin = /var/lib/mysql/mysql-binlog_error = /var/lib/mysql/mysql-error.log log_queries_not_using_

7、indexes = 1#secondlong_query_time = 10 slow_query_log = 1 slow_query_log_file = /var/lib/mysql/mysql-slow.logserver-id= 1binlog_format=mixedmysqldumpquickmax_allowed_packet = 16Mmysqlno-auto-rehashdefault-character-set=utf8myisamchkkey_buffer_size = 256Msort_buffer_size = 256Mread_buffer = 2Mwrite_b

8、uffer = 2Mmysqlhotcopyinteractive-timeout啟動(dòng)MySQLrootlocalhost mysql55# service mysql start8. 初始化MySQL root用戶密碼，比如設(shè)置密碼為：123456rootlocalhost mysql# mysql_secure_installation9. 登錄mysql后臺(tái)管理程序rootlocalhost mysql# mysql -u root -p123456mysql10. 添加新賬號(hào)mysql grant all on *.* to dtoa% identified by dtoa123456

9、;Query OK, 0 rows affected (0.00 sec)mysql flush privileges;Query OK, 0 rows affected (0.00 sec)11. 設(shè)置為開(kāi)機(jī)自動(dòng)啟動(dòng)rootlocalhost mysql# chkconfig mysql on2 生產(chǎn)環(huán)境下MySQL數(shù)據(jù)庫(kù)主從同步配置2.1 主數(shù)據(jù)庫(kù)配置2.1.1.檢查mysql的配置文件：# vi /etc/f在mysqld這個(gè)Section確認(rèn)以下配置log_bin = /var/lib/mysql/mysql-bin #配置在數(shù)據(jù)庫(kù)有變動(dòng)時(shí)寫(xiě)二進(jìn)制日志server-id = 1 #標(biāo)識(shí)

10、，唯一，值范圍在：1至223-1binlog-do-db = dtoa # 要給從機(jī)同步的庫(kù)保存退出。重啟mysql 服務(wù)。# Service mysql restart2.1.2. 進(jìn)入mysql配置：登錄mysql后臺(tái)管理程序# mysql -u root -p123456添加新賬號(hào)mysql grant all on *.* to dtoa% identified by dtoa123456;鎖主庫(kù)表：mysqlflush tables with read lock;顯示主庫(kù)信息，記錄file和position，從庫(kù)設(shè)置會(huì)用到。mysqlshow master status;+-+-+-

11、+-+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-+-+-+-+| mysql-bin.000004 | 106 | | | +-+-+-+-+退出mysqlmysqlquit;2.1.3. linux Shell中對(duì)數(shù)據(jù)庫(kù)的操作備份主機(jī)上的dtoa數(shù)據(jù)庫(kù)：#mysqldump -master-data u root p dtoa dtoa.sql把備份文件傳到從機(jī)#rsync -vzrtopg dtoa.sql root從機(jī)ip:/sw/進(jìn)入mysql，解鎖主庫(kù)表# mysql -u root -p123456mysqlunl

12、ock tables;注：配置文件還可以配置的參數(shù)可根據(jù)情況選擇設(shè)置：mysqldbinlog-do-db = db2 # 要給從機(jī)同步的庫(kù) binlog-ignore-db = mysql # 不給從機(jī)同步的庫(kù) expire_logs_days = 2 # 自動(dòng)清理 2 天前的log文件，可根據(jù)需要修改2.2 從數(shù)據(jù)庫(kù)配置檢查mysql的配置文件： # vi /etc/f在mysqld這個(gè)Section確認(rèn)以下配置log_bin = /var/lib/mysql/mysql-bin #配置在數(shù)據(jù)庫(kù)有變動(dòng)時(shí)寫(xiě)二進(jìn)制日志server-id = 2 #標(biāo)識(shí)，唯一，值范圍在：1至223-1repli

13、cate-do-db =dtoa #如果不設(shè)置就是要同步所有數(shù)據(jù)庫(kù) 保存退出。重啟mysql 服務(wù)。# Service mysql restart#mysql -u root p 輸入密碼進(jìn)入mysqlMysqlcreate database dtoa; 創(chuàng)建dtoa數(shù)據(jù)庫(kù)Mysqlquit;導(dǎo)入dtoa.sql數(shù)據(jù)：#mysql -u root -p dtoa stop slave;mysql change master to master_host=主庫(kù)ip, master_user=dtoa,master_password=dtoa123456,master_log_file=mysql

14、-bin.000004,master_log_pos=106; #后兩個(gè)就是剛才記錄的主庫(kù)file和position值mysql start slave;注：配置文件還可以配置的參數(shù)，可根據(jù)情況選擇：mysqldreplicate-ignore-db = mysql # 不從主機(jī)同步的數(shù)據(jù)庫(kù)(多個(gè)寫(xiě)多行) replicate-do-db = db1 # 要從主機(jī)同步的庫(kù)(多個(gè)寫(xiě)多行) replicate-do-db = db2 # 要從主機(jī)同步的庫(kù)(多個(gè)寫(xiě)多行) log-slave-update # 啟用從機(jī)服務(wù)器上的slave日志功能，使這臺(tái)計(jì)算機(jī)可以#用來(lái)構(gòu)成一個(gè)鏡像鏈(A-B-C) sl

15、ave-skip-errors # 跳過(guò)錯(cuò)誤3 監(jiān)控服務(wù)器3.1 監(jiān)控主數(shù)據(jù)庫(kù)服務(wù)器運(yùn)行于主數(shù)據(jù)庫(kù)mysql show master status;+-+-+-+-+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-+-+-+-+| mysql-bin.000004 | 106 | | | +-+-+-+-+1 row in set (0.00 sec) 其中File是表示日志記錄的文件，而Position則是表示當(dāng)前日志在文件中的位置，這個(gè)也是從數(shù)據(jù)庫(kù)服務(wù)器上執(zhí)行復(fù)制操作必須的標(biāo)識(shí)，后面的兩個(gè)字段分別表示要記錄的數(shù)據(jù)庫(kù)名稱和不需要記

16、錄的數(shù)據(jù)庫(kù)名稱，我們也可以在配置文件中進(jìn)行配置。3.2 監(jiān)控從數(shù)據(jù)庫(kù)服務(wù)器運(yùn)行于從數(shù)據(jù)庫(kù)mysql show slave statusG;* 1. row * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.117.128 Master_User: dtoa Master_Port: 8819 Connect_Retry: 30 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 106 Relay_Log_File: mail-relay-bi

17、n.000221 Relay_Log_Pos: 15149444 Relay_Master_Log_File: mysql-bin.000009 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: dtoa Replicate_Ignore_DB: 部分釋義:Slave_IO_State:線程已經(jīng)連接上主服務(wù)器，正等待二進(jìn)制日志事件到達(dá)。Master_Host:主機(jī)IPMaster_Log_File和Read_Master_Log_Pos選項(xiàng)執(zhí)行CHANGE MASTER TO來(lái)告訴從服務(wù)器重新從該點(diǎn)讀取二進(jìn)制日志Slave_IO_Running:是否要從Master復(fù)制二進(jìn)制數(shù)據(jù)Slave_SQL_Running:是否執(zhí)行從Master復(fù)制過(guò)來(lái)的二進(jìn)制數(shù)據(jù)Slave_IO_Running和Slave_SQL_Running的值均為Yes時(shí)為同步開(kāi)啟;Last_Errno和Last_Error是錯(cuò)誤信息的提示（如果有錯(cuò)誤）Seconds_Behind_Master：Slave的數(shù)據(jù)落后Master的多少秒，同步完成為0注：如果從數(shù)據(jù)庫(kù)服務(wù)器在同步的過(guò)程中出現(xiàn)了問(wèn)題，那么我們可以通過(guò)reset slave來(lái)重置從