openstack主從模式數(shù)據(jù)庫主從模式實(shí)現(xiàn)2

數(shù)據(jù)庫主從模式實(shí)現(xiàn)22一、準(zhǔn)備工作：1.主從數(shù)據(jù)庫版本最好一致2.主從數(shù)據(jù)庫內(nèi)數(shù)據(jù)保持一致主數(shù)據(jù)庫：/linux從數(shù)據(jù)庫：/linux3二、主數(shù)據(jù)庫master修改：1.修改mysql配置找到主數(shù)據(jù)庫的配置文件f(或者my.ini)，我的在/etc/mysql/f,在[mysqld]部分插入如下兩行：[mysqld]log-bin=mysql-bin#開啟二進(jìn)制日志server-id=1#設(shè)置server-id2.重啟mysql，創(chuàng)建用于同步的用戶賬號(hào)打開mysql會(huì)話shell>mysql-hlocalhost-uname-ppassword創(chuàng)建用戶并授權(quán)：用戶：zyjy密碼：slavepassmysql>CREATEUSER'zyjy'@''IDENTIFIEDBY'slavepass';#創(chuàng)建用戶mysql>GRANTREPLICATIONSLAVEON*.*TO'zyjy'@'';#分配權(quán)限mysql>flushprivileges;#刷新權(quán)限4查看master狀態(tài)3.查看master狀態(tài)，記錄二進(jìn)制文件名(mysql-bin.000003)和位置(73)：mysql>SHOWMASTERSTATUS;+------------------+----------+--------------+------------------+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|+------------------+----------+--------------+------------------+|mysql-bin.000003|73|test|manual,mysql|+------------------+----------+--------------+------------------+5三、從服務(wù)器slave修改：1.修改mysql配置同樣找到f配置文件，添加server-id[mysqld]server-id=2#設(shè)置server-id，必須唯一2.重啟mysql，打開mysql會(huì)話，執(zhí)行同步SQL語句(需要主服務(wù)器主機(jī)名，登陸憑據(jù)，二進(jìn)制文件的名稱和位置)：mysql>CHANGEMASTERTO->MASTER_HOST='',->MASTER_USER='zyjy',->MASTER_PASSWORD='slavepass',->MASTER_LOG_FILE='mysql-bin.000003',->MASTER_LOG_POS=73;3.啟動(dòng)slave同步進(jìn)程：mysql>startslave;6查看slave狀態(tài)4.查看slave狀態(tài)：mysql>showslavestatus\G;***************************1.row***************************Slave_IO_State:WaitingformastertosendeventMaster_Host:Master_User:zyjyMaster_Port:3306Connect_Retry:60Master_Log_File:mysql-bin.000013Read_Master_Log_Pos:11662Relay_Log_File:mysqld-relay-bin.000022Relay_Log_Pos:11765Relay_Master_Log_File:mysql-bin.000013Slave_IO_Running:YesSla