CentOS mysql 主從配置.docx

MySQL 安裝安裝包（版本）：apache-tomcat-6.0.41.tar.gz安裝包可以從 01/102 /usr/local/ 目錄下獲取，或通過(guò)網(wǎng)絡(luò)下載。安裝過(guò)程：百度搜索 “Linux mysql tar.gz 安裝”主從配置主數(shù)據(jù)庫(kù) - 修改配置文件# vi /etc/f clientport=3306mysqldport=3306basedir = /usr/local/mysqldatadir = /usr/local/mysql/datacharacter-set-server=utf8log-error = /usr/local/mysql/data/error.logpid-file = /usr/local/mysql/data/mysql.piduser = mysqlsocket=/usr/local/mysql/mysql.sock#主從配置（主）log-bin=mysql-bin #啟用二進(jìn)制日志server-id=101 #服務(wù)器唯一ID，默認(rèn)取IP最后一位log-bin=/var/log/mysql/updatelog #設(shè)定生成log文件名，文件路徑需要手動(dòng)創(chuàng)建并授權(quán)給mysql用戶(hù)權(quán)限binlog-do-db=zjsz_full #二進(jìn)制需要同步的數(shù)據(jù)庫(kù)名稱(chēng)binlog-ignore-db=mysql,test #避免同步mysql用戶(hù)配置expire-logs-days=7 #只保留7天的二進(jìn)制日志mysqldefault-character-set=utf8socket=/usr/local/mysql/mysql.sockmysqldumpsocket=/usr/local/mysql/mysql.sockmysqladminsocket=/usr/local/mysql/mysql.sock主數(shù)據(jù)庫(kù) - 重啟服務(wù)# mysqld restart主數(shù)據(jù)庫(kù) - 授權(quán)從數(shù)據(jù)庫(kù)# mysql uroot pmysql use mysql;注意：如果提示沒(méi)有權(quán)限或報(bào)錯(cuò)信息，那么可能是 root 權(quán)限在安裝過(guò)程中丟失了，先退出，恢復(fù) root 權(quán)限后再操作。mysql GRANT REPLICATION SLAVE ON *.* to root02 identified by mysql;mysql FLUSH TABLES WITH READ LOCK;注意：上面給數(shù)據(jù)庫(kù)加鎖了，在此狀態(tài)下數(shù)據(jù)庫(kù)只能進(jìn)行查詢(xún)操作。mysql show master status;+-+-+-+-+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-+-+-+-+| updatelog.000008 | 101800607 | zjsz_full | mysql,test |+-+-+-+-+1 row in set (0.00 sec)注意：拷貝 File 和 Postion 的值從數(shù)據(jù)庫(kù) 數(shù)據(jù)備份將主數(shù)據(jù)庫(kù)上的數(shù)據(jù)備份到從數(shù)據(jù)庫(kù)上從數(shù)據(jù)庫(kù) 修改配置文件# vi /etc/f clientport=3306mysqldport=3306basedir = /usr/local/mysqldatadir = /usr/local/mysql/datacharacter-set-server=utf8log-error = /usr/local/mysql/data/error.logpid-file = /usr/local/mysql/data/mysql.piduser = mysqlsocket=/usr/local/mysql/mysql.sock#主從配置（從）log-bin=mysql-bin #啟用二進(jìn)制日志server-id=102 #服務(wù)器唯一id，默認(rèn)取IP最后一段expire_logs_days=7replicate-ignore-db=mysql #屏蔽對(duì)mysql庫(kù)的同步replicate-do-db=zjsz_full #同步數(shù)據(jù)庫(kù)的名稱(chēng)mysqldefault-character-set=utf8socket=/usr/local/mysql/mysql.sockmysqldumpsocket=/usr/local/mysql/mysql.sockmysqladminsocket=/usr/local/mysql/mysql.sock從數(shù)據(jù)庫(kù) 重啟服務(wù)# mysqld restart從數(shù)據(jù)庫(kù) 執(zhí)行同步語(yǔ)句# mysql uroot pmysql use mysql;mysql change master tomaster_host=01,master_user=root,master_password=mysql,master_log_file= updatelog.000008,master_log_pos=101800607;注意：此處的 master_log_file 和 master_log_pos 就是主數(shù)據(jù)庫(kù)的 File 和 Postion 的值。從數(shù)據(jù)庫(kù) 啟動(dòng) slavemysql start slave;從數(shù)據(jù)庫(kù) 主從同步檢查mysql show slave status G;* 1. row *Slave_IO_State: Waiting for master to send eventMaster_Host: 01Master_User: rootMaster_Port: 3306Connect_Retry: 60Master_Log_File: updatelog.000008Read_Master_Log_Pos: 101800607Relay_Log_File: mysql-relay-bin.000002Relay_Log_Pos: 11015385Relay_Master_Log_File: updatelog.000008Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: zjsz_fullReplicate_Ignore_DB: mysqlReplicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 101800607Relay_Log_Space: 11015541Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1011 row in set (0.00 sec)注意：Slave_IO_Running 和 Slave_SQL_Running 都為 YES 時(shí)，主從同步配置完成，如果為NO，則需要查看相應(yīng)的報(bào)錯(cuò)信息。主數(shù)據(jù)庫(kù) 解鎖mysql UNLOCK TABLES;同步錯(cuò)誤處理先鎖住主數(shù)據(jù)庫(kù)，獲取主數(shù)據(jù)庫(kù)的 File 和 Postion 數(shù)據(jù)。登錄從數(shù)據(jù)庫(kù)，停止同步（stop slave），重新執(zhí)行同步語(yǔ)句。退出，重啟從數(shù)據(jù)庫(kù)。最后，解鎖主數(shù)據(jù)