主從復制配置
####################主服務器配置(192.168.56.149)###########################
1、修改主數據庫mysql配置,找到主數據庫的配置文件my.cnf(或者my.ini),我的在/etc/mysql/my.cnf,在[mysqld]部分插入如下兩行
[mysqld]
redis主從復制原理,log-bin=mysql-bin #開啟二進制日志
server-id=1 #設置server-id
2、重啟mysql,創建用于同步的用戶賬號
CREATE USER 'test'@'192.168.56.150' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON*.* TO 'test'@'192.168.56.150';
mysql主從搭建。flush privileges;
3、查看master裝填,記錄二進制文件名(mysql-bin.000005)和位置(977)
mysql>show master status;+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
mysql主主復制?| mysql-bin.000005 | 977 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
#########################從服務器(ip:192.168.56.150)##################################
1、修改mysql配置,同樣找到my.cnf配置文件,添加server-id
cookie和session區別。[mysqld]
server-id=2 #設置server-id,必須唯一
2、重啟mysql,打開mysql會話,執行同步SQL語句(需要主服務器主機名,登陸憑據,二進制文件的名稱和位置)
CHANGE MASTER TO
MASTER_HOST='192.168.56.149',
mysql mgr、MASTER_USER='test',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000005',
MASTER_LOG_POS=516;
3、啟動slave同步進程
mysql、start slave;
注意:關閉是stop slave;
4、查看slave狀態
show slave status\G;
FAQ
mysql主從復制的理解和搭建?1、如果Slave_IO_Running: Connecting,同時Last_IO_Errno: 2003,說明和主節點服務器無法通信
Relay_Master_Log_File: mysql-bin.000005Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
mysql 主從配置、Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno:0Last_Error:
mysql主從切換步驟?Skip_Counter:0Exec_Master_Log_Pos:120Relay_Log_Space:120Until_Condition: None
Until_Log_File:
Until_Log_Pos:0Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
mysql主從配置詳解?Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert: No
Last_IO_Errno:2003Last_IO_Error: error connecting to master'test@192.168.56.149:3306' - retry-time: 60 retries: 1Last_SQL_Errno:0Last_SQL_Error:
將一個數據庫表復制到另一個數據庫?Replicate_Ignore_Server_Ids:
Master_Server_Id:0Master_UUID:
Master_Info_File:/var/lib/mysql/master.infoSQL_Delay:0SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waitingfor the slave I/O thread to update it
解決方案:
1、ping主機是否通
2、從節點的設置賬號和密碼以及主節點IP是否正確 ,即MASTER_USER、MASTER_PASSWORD、MASTER_HOST、MASTER_LOG_FILE、MASTER_LOG_POS是否一致
3、關閉防火墻? 即systemctl disable firewalld
4、關閉selinux 即selinux=disabled
版权声明:本站所有资料均为网友推荐收集整理而来,仅供学习和研究交流使用。
工作时间:8:00-18:00
客服电话
电子邮件
admin@qq.com
扫码二维码
获取最新动态