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

Mysql數(shù)據(jù)庫安裝及生產(chǎn)環(huán)境下主從庫同步配置目 錄1安裝Mysql數(shù)據(jù)庫22生產(chǎn)環(huán)境下MySQL數(shù)據(jù)庫主從同步配置52.1 主數(shù)據(jù)庫配置 .52.2 從數(shù)據(jù)庫配置 53 監(jiān)控服務(wù)器63.1 監(jiān)控主數(shù)據(jù)庫服務(wù)器.63.2 監(jiān)控從數(shù)據(jù)庫服務(wù)器 .61 安裝Mysql數(shù)據(jù)庫安裝環(huán)境：系統(tǒng)： CentOS-6.6-x86_64數(shù)據(jù)庫： 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/mysql553. 上傳安裝文件到上一步創(chuàng)建的目錄4. 檢查是否已安裝過MySQLrootlocalhost /# rpm -qa | grep -i mysqlMySQL-client-5.5.42-1.el6.x86_64MySQL-server-5.5.42-1.el6.x86_645. 如果已安裝則移除，否則請?zhí)^此步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的開發(fā)頭文件和庫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.rpmPreparing. # 100% 1:MySQL-server # 100%7. 配置MySQLrootlocalhost mysql55# cp /usr/share/mysql/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_names=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 = 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_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_buffer = 2Mmysqlhotcopyinteractive-timeout啟動MySQLrootlocalhost mysql55# service mysql start8. 初始化MySQL root用戶密碼，比如設(shè)置密碼為：123456rootlocalhost mysql# mysql_secure_installation9. 登錄mysql后臺管理程序rootlocalhost mysql# mysql -u root -p123456mysql10. 添加新賬號mysql grant all on *.* to dtoa% identified by dtoa123456;Query OK, 0 rows affected (0.00 sec)mysql flush privileges;Query OK, 0 rows affected (0.00 sec)11. 設(shè)置為開機(jī)自動啟動rootlocalhost mysql# chkconfig mysql on2 生產(chǎn)環(huán)境下MySQL數(shù)據(jù)庫主從同步配置2.1 主數(shù)據(jù)庫配置2.1.1.檢查mysql的配置文件：# vi /etc/f在mysqld這個(gè)Section確認(rèn)以下配置log_bin = /var/lib/mysql/mysql-bin #配置在數(shù)據(jù)庫有變動時(shí)寫二進(jìn)制日志server-id = 1 #標(biāo)識，唯一，值范圍在：1至223-1binlog-do-db = dtoa # 要給從機(jī)同步的庫保存退出。重啟mysql 服務(wù)。# Service mysql restart2.1.2. 進(jìn)入mysql配置：登錄mysql后臺管理程序# mysql -u root -p123456添加新賬號mysql grant all on *.* to dtoa% identified by dtoa123456;鎖主庫表：mysqlflush tables with read lock;顯示主庫信息，記錄file和position，從庫設(shè)置會用到。mysqlshow master status;+-+-+-+-+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-+-+-+-+| mysql-bin.000004 | 106 | | | +-+-+-+-+退出mysqlmysqlquit;2.1.3. linux Shell中對數(shù)據(jù)庫的操作備份主機(jī)上的dtoa數(shù)據(jù)庫：#mysqldump -master-data u root p dtoa dtoa.sql把備份文件傳到從機(jī)#rsync -vzrtopg dtoa.sql root從機(jī)ip:/sw/進(jìn)入mysql，解鎖主庫表# mysql -u root -p123456mysqlunlock tables;注：配置文件還可以配置的參數(shù)可根據(jù)情況選擇設(shè)置：mysqldbinlog-do-db = db2 # 要給從機(jī)同步的庫 binlog-ignore-db = mysql # 不給從機(jī)同步的庫 expire_logs_days = 2 # 自動清理 2 天前的log文件，可根據(jù)需要修改2.2 從數(shù)據(jù)庫配置檢查mysql的配置文件： # vi /etc/f在mysqld這個(gè)Section確認(rèn)以下配置log_bin = /var/lib/mysql/mysql-bin #配置在數(shù)據(jù)庫有變動時(shí)寫二進(jìn)制日志server-id = 2 #標(biāo)識，唯一，值范圍在：1至223-1replicate-do-db =dtoa #如果不設(shè)置就是要同步所有數(shù)據(jù)庫 保存退出。重啟mysql 服務(wù)。# Service mysql restart#mysql -u root p 輸入密碼進(jìn)入mysqlMysqlcreate database dtoa; 創(chuàng)建dtoa數(shù)據(jù)庫Mysqlquit;導(dǎo)入dtoa.sql數(shù)據(jù)：#mysql -u root -p dtoa stop slave;mysql change master to master_host=主庫ip, master_user=dtoa,master_password=dtoa123456,master_log_file=mysql-bin.000004,master_log_pos=106; #后兩個(gè)就是剛才記錄的主庫file和position值mysql start slave;注：配置文件還可以配置的參數(shù)，可根據(jù)情況選擇：mysqldreplicate-ignore-db = mysql # 不從主機(jī)同步的數(shù)據(jù)庫(多個(gè)寫多行) replicate-do-db = db1 # 要從主機(jī)同步的庫(多個(gè)寫多行) replicate-do-db = db2 # 要從主機(jī)同步的庫(多個(gè)寫多行) log-slave-update # 啟用從機(jī)服務(wù)器上的slave日志功能，使這臺計(jì)算機(jī)可以#用來構(gòu)成一個(gè)鏡像鏈(A-B-C) slave-skip-errors # 跳過錯誤3 監(jiān)控服務(wù)器3.1 監(jiān)控主數(shù)據(jù)庫服務(wù)器運(yùn)行于主數(shù)據(jù)庫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ù)庫服務(wù)器上執(zhí)行復(fù)制操作必須的標(biāo)識，后面的兩個(gè)字段分別表示要記錄的數(shù)據(jù)庫名稱和不需要記錄的數(shù)據(jù)庫名稱，我們也可以在配置文件中進(jìn)行配置。3.2 監(jiān)控從數(shù)據(jù)庫服務(wù)器運(yùn)行于從數(shù)據(jù)庫mysql show slave statusG;* 1. row * Slave_IO_State: Waiting for master to send event Master_Host: 28 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-bin.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來告訴從服務(wù)器重新從該點(diǎn)讀取二進(jìn)制日志Slave_IO_Running:是否要從Master復(fù)制二進(jìn)制數(shù)據(jù)Slave_SQL_Running:是否執(zhí)行從Master復(fù)制過來的二進(jìn)制數(shù)據(jù)Slave_IO_Running和Slave_SQL_Running的值均為Yes時(shí)為同步開啟;Last_Errno和Last_Error是錯誤信息的提示（如果有錯誤）Seconds_Behind_Master：Slave的數(shù)據(jù)落后Master的多少秒，同步完成為0注：如果從數(shù)據(jù)庫服務(wù)器在同步的過程中出現(xiàn)了問題，那么我們可以通過reset slave來重