MySQL主從復制
主從復制原理：
mysql主從同步其實是一個異步復制的過程,要實現復制首先在master上開啟bin-log日志功能。整個過程需要開啟3個線程, 分別是master開啟IO線程,slave開啟IO線程和SQL線程. (1) 在slave服務器執行start slave,服務器的IO線程的請求后,master服務器的IO線程根據slave服務器發送指定的bin-log日志之后得內容, 然后返回給slave端的IO線程;(返回的信息中除了bin-log日志內容外,還有本地返回日志內容后再master服務器的新的binlog文件 及在binlog中的下一個指定更新位置) (2) slave的IO線程接收到信息后,將接受到的日志一次添加到slave端的relay-log文件的最末端,并將讀取到的master端的bin-log的文件名 和位置記錄到master-info文件中,以便在下一次讀取的時候能夠清楚的告訴master, "我需要從某個bin-log的哪個位置開始往后的日志內容,請發給我;" (3) slave的SQL線程檢測到relay-log中新增加了內容后,會馬上解析relay-log的內容成為master端真實執行 時間的那些可執行的內容,并且重新回放.
安裝其實很簡單,主要得理解原理。
環境：mariadb-5.5.44源碼
mysql 主:192.168.155.12
mysql 從:192.168.155.13
實現mysql主從同步,以下是mysql主從操作步驟、（源碼環境以及按照好）
mysql安裝目錄 /usr/local/mysql mysql數據目錄/mydata/data mysql配置文件/etc/my.cnf
在mysql主上，操作如下：
# vim /etc/my.cnf
[client] port = 3306 socket = /tmp/mysql.sock [mysqld] port = 3306 socket = /tmp/mysql.sock skip-external-locking key_buffer_size = 256M max_allowed_packet = 1M table_open_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size= 16M thread_concurrency = 8 datadir = /mydata/data ##mysql數據目錄 innodb_file_per_table = on ##InnoDB為獨立表空間模式，每一個數據庫，每一個表都會生成一個數據空間. skip_name_resolve = on ##關閉dns反解 log-bin=mysql-bin ##binlog文件名字,文件默認存放于數據目錄中，即：/mydata/data binlog_format=row ##binlog格式為基于行的復制 server-id = 1 ##master必須指定唯一server_id sync_binlog = 1 ##及時寫入bin-log日志 log_error= /mydata/data/error.log ##mysql錯誤日志 [mysqld-safe] pid-file=/mydata/data/mysqld.pid ##mysql的pid存放路徑 replicate-do-db =all ##同步全部的數據庫，如果只同步某一個數據庫，改成數據庫名稱就可以. [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 128M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout
# /etc/init.d/mysqld restart # mysql MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.155.13' IDENTIFIED BY 'replpass'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> show master status \G *************************** 1. row *************************** File: mysql-bin.000004 Position: 500 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.00 sec)
在mysql從上，操作如下：
# vim /etc/my.cnf [client] port = 3306 socket = /tmp/mysql.sock [mysqld] port = 3306 socket = /tmp/mysql.sock skip-external-locking key_buffer_size = 256M max_allowed_packet = 1M table_open_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size= 16M thread_concurrency = 8 datadir = /mydata/data innodb_file_per_table = on skip_name_resolve = on relay-log=relay-log ##開啟中繼日志，中繼日志名為relay-log，默認存放于數據目錄/mydata/data下 relay-log-index=relay-log.index ##定義relay_log的位置和名稱 binlog_format=row server-id = 2 ##slave必須指定唯一server_id sync_binlog = 1 log_error= /mydata/data/error.log [mysqld-safe] pid-file=/mydata/data/mysqld.pid replicate-do-db =all [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 128M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout
# /etc/init.d/mysqld restart # mysql MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.155.12',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=500; Query OK, 0 rows affected (0.04 sec) MariaDB [(none)]> show slave status \G *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.155.12 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 500 Relay_Log_File: relay-log.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: No ##沒開啟IO線程 Slave_SQL_Running: No ##沒開啟SQL線程 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: 500 Relay_Log_Space: 245 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: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 1 row in set (0.00 sec) MariaDB [(none)]> start slave ; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> show slave status \G *************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: 192.168.155.12 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 500 Relay_Log_File: relay-log.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Connecting 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: 500 Relay_Log_Space: 245 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: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 2003 Last_IO_Error: error connecting to master 'repluser@192.168.155.12:3306' - retry-time: 60 retries: 86400 message: Can't connect to MySQL server on '192.168.155.12' (113) Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 1 row in set (0.00 sec) 問題：為什么開啟IO/SQL線程還是不能同步; 解決思路：檢查下iptables是否關閉，ip:port是否通了 # /etc/init.d/iptables stop iptables: Setting chains to policy ACCEPT: filter [ OK ] iptables: Flushing firewall rules: [ OK ] iptables: Unloading modules: [ OK ] 關閉防火墻之后，進入mysql查看下slave狀態; # mysql MariaDB [(none)]> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.155.12 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 500 Relay_Log_File: relay-log.000002 Relay_Log_Pos: 529 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes ##代表已經開啟IO線程 Slave_SQL_Running: Yes ##代表已經開啟SQL線程 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: 500 Relay_Log_Space: 817 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: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 1 row in set (0.00 sec) mysql主從已經搭建好，接下來就是測試主從復制是否成功;
測試階段：
在master上操作： MariaDB [(none)]> create database mydb; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> use mydb; Database changed MariaDB [mydb]> create table tb1 (id int , name varchar(30), age int); Query OK, 0 rows affected (0.02 sec) MariaDB [mydb]> insert into tb1 (id, name, age) values (1,'hx',20),(2,'yl','21'); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [mydb]> select * from tb1; +------+------+------+ | id | name | age | +------+------+------+ | 1 | hx | 20 | | 2 | yl | 21 | +------+------+------+ 2 rows in set (0.00 sec)
在slave上測試，查看是否同步過來. MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mydb | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) MariaDB [(none)]> use mydb; Database changed MariaDB [mydb]> show tables; +----------------+ | Tables_in_mydb | +----------------+ | tb1 | +----------------+ 1 row in set (0.00 sec) MariaDB [mydb]> select * from tb1; +------+------+------+ | id | name | age | +------+------+------+ | 1 | hx | 20 | | 2 | yl | 21 | +------+------+------+ 2 rows in set (0.00 sec) MariaDB [mydb]> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.155.12 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 893 Relay_Log_File: relay-log.000002 Relay_Log_Pos: 922 Relay_Master_Log_File: mysql-bin.000004 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: 893 Relay_Log_Space: 1210 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: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 1 row in set (0.00 sec) slave以及同步了maste的數據庫、表、以及數據.
結論： 因為數據量很小，雖然是個異步復制的過程，但是我們可以感覺到復制的速度很快，和同步沒有兩樣。 在我們的生產環境，也是使用mysql主從數據。因為insert,update,delete是操作會比較頻繁，會有5-10秒的延遲時間. 主從同步的過程中，也會出現主從數據不一致的問題。這樣可能就需要你重新做一遍主從復制了。 有些地方如果理解有錯誤，可以評論下本文章，一起努力學習及分享。 作者: kattall Q Q : 532461968 感謝: MageEdu
原創文章，作者：Net20_赤羽，如若轉載，請注明出處：http://www.www58058.com/20082
