一、mysql主從方式:
同步:主服務器有數據寫入,存儲在硬盤,記錄二進制日志一份。二進制日志完整發送給從服務器記錄於中繼日志中,從服務器回應主服務器已收到,
異步:主服務器寫入數據,存儲磁盤,記錄二進制日志,直接反饋客戶端;至於從服務器收到與否,不予理會
mysql的復制默認為異步模式
二、配置主從服務器
1.先在兩台機器上分別安裝好mysql,版本需要一致,然後配置,啟動
2.主服務器配置
1).查看mysql是否正常啟動
netstat -nltp | grep 3306
2)修改配置文件,開啟二進制日志
2.1.vim /etc/my.cnf
將
log-bin=mysql-bin改為log-bin=/usr/local/mysql/var/binlogs/master-bin
保存
2.2.創建二進制日志目錄
mkdir -p /usr/local/mysql/var/binlogs/
2.3.對目錄權限進行授權
chown mysql.mysql /usr/local/mysql/var/binlogs/
2.4.重啟mysql服務
service mysqld restart
3)創建有復制權限的賬號
mysql>grant replication slave,replication client on *.* to 'replication'@'192.168.5.10' identified by 'Looking_s123';
mysql>flush privileges;
3.從服務器配置
1)查看mysql是否正常啟動
netstat -ntlp | grep 3306
2)修改配置文件
vim /etc/my.cnf
將二進制日志文件注釋
log-bin=mysql-bin
binlog_format=mixed
修改server-id
將
server-id =1
修改為
server-id =2
開啟中繼日志
relay-log =/usr/local/mysql/var/relaylogs/relaylogs
創建中繼日志目錄及授權
mkdir -p /usr/local/mysql/var/relaylogs/
chown -R mysql.mysql /usr/local/mysql/var/relaylogs/
3)重啟服務,確保中繼日志啟動
service mysqld restart
4)查看中繼日志啟動狀態
mysql> show global variables like '%relay%';
+-----------------------+------------------------------------------+
| Variable_name | Value |
+-----------------------+------------------------------------------+
| max_relay_log_size | 0 |
| relay_log | /usr/local/mysql/var/relaylogs/relaylogs |
| relay_log_index | |
| relay_log_info_file | relay-log.info |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
+-----------------------+------------------------------------------+
6 rows in set (0.00 sec)
5)連接主服務器
mysql>change master to master_host='192.168.5.11',master_user='replication',master_password='Looking_s123';
6)手動啟動復制線程
mysql>start slave;
7)查看主從狀態
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.5.11
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 317
Relay_Log_File: relaylogs.000005
Relay_Log_Pos: 463
Relay_Master_Log_File: master-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 317
Relay_Log_Space: 999
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
ERROR:
No query specified
三.測試
在主服務器上創建數據庫和表,在從服務器上查看復制效果,如果同步了,就說明配置成功了!