配置Mysql數據庫的主從同步教程詳情

2024-07-24 12:35:24

字體：大中小

來源：轉載

供稿：網友

　　主從同步是實現網站分布式數據處理一個非常常用的方案了,今天我來為各位介紹配置Mysql數據庫的主從同步(雙主)教程,希望下文能幫助到各位哦.

　　配置Mysql數據庫的主從同步(一主一從).

　　一、主庫開啟BINLOG、server-id

　　[root@Master-Mysql ~]# grep -E "server-id|log-bin" /etc/my.cnf
　　log-bin = /usr/local/mysql/data/mysql-bin
　　server-id = 1
　　mysql> show variables like '%log_bin%';
　　+---------------------------------+---------------------------------------+
　　| Variable_name                   | Value                                 |
　　+---------------------------------+---------------------------------------+
　　| log_bin                         | ON                                    |
　　| log_bin_basename                | /usr/local/mysql/data/mysql-bin       |
　　| log_bin_index                   | /usr/local/mysql/data/mysql-bin.index |
　　| log_bin_trust_function_creators | OFF                                   |
　　| log_bin_use_v1_row_events       | OFF                                   |
　　| sql_log_bin                     | ON                                    |
　　+---------------------------------+---------------------------------------+
　　6 rows in set (0.01 sec) --phpfensi.com
　　mysql> show variables like '%server_id%';
　　+----------------+-------+
　　| Variable_name | Value |
　　+----------------+-------+
　　| server_id      | 1     |
　　| server_id_bits | 32    |
　　+----------------+-------+
　　2 rows in set (0.00 sec)
　　備注:以上兩個信息必須在[mysqld]模塊下！！！

　　二、給從庫授權

　　mysql> grant replication slave on *.* to byrd@'192.168.199.%' identified by 'admin';
　　mysql> flush privileges;
　　mysql> select user,host from mysql.user;
　　+------+---------------+
　　| user | host          |
　　+------+---------------+
　　| root | 127.0.0.1     |
　　| byrd | 192.168.199.% |
　　| root | ::1           |
　　| root | lamp          |
　　| root | localhost     |
　　+------+---------------+
　　5 rows in set (0.00 sec)
　　鎖表前建立點數據:

　　mysql> create database hitest;
　　mysql> show databases;
　　+--------------------+
　　| Database           |
　　+--------------------+
　　| hitest             |
　　+--------------------+
　　6 rows in set (0.00 sec)
　　mysql> use hitest;
　　mysql> create table test(
　　    -> id int(4) not null primary key auto_increment,
　　    -> name char(20) not null
　　    -> );
　　Query OK, 0 rows affected (1.80 sec)
　　mysql> show tables ;
　　+------------------+
　　| Tables_in_hitest |
　　+------------------+
　　| test             |
　　+------------------+
　　mysql> insert into test(id,name) values(1,'zy');
　　mysql> select * from test;
　　+----+------+
　　| id | name |
　　+----+------+
　　| 1 | zy   |
　　+----+------+
　　三、鎖表、備份、解鎖

　　mysql> flush table with read lock;    #鎖表
　　mysql> show variables like '%timeout%';    #鎖表時間
　　+-----------------------------+----------+
　　| Variable_name               | Value    |
　　+-----------------------------+----------+
　　| interactive_timeout         | 28800    |
　　| wait_timeout                | 28800    |
　　+-----------------------------+----------+
　　12 rows in set (0.06 sec)
　　mysql> show master status;    #binlog日志位置
　　+------------------+----------+--------------+------------------+-------------------+
　　| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
　　+------------------+----------+--------------+------------------+-------------------+
　　| mysql-bin.000004 |     1305 |              |                  |                   |
　　+------------------+----------+--------------+------------------+-------------------+
　　1 row in set (0.03 sec)
　　[root@Master-Mysql ~]# /usr/local/mysql/bin/mysqldump -uroot -p'' -B -A |gzip >/tmp/all.sql.gz    #新窗口備份
　　Enter password:
　　mysql> unlock table;    #解鎖
　　###############解鎖后主庫操作如下：###############
　　mysql> use hitest
　　mysql> insert into test(id,name) values(2,'binghe');
　　mysql> select * from test;
　　+----+--------+
　　| id | name   |
　　+----+--------+
　　| 1 | zy     |
　　| 2 | binghe |
　　+----+--------+
　　mysql> create database hxy;
　　###############解鎖后主庫操作完成~###############
　　備注:備份數據需要重新打開新窗口,不然鎖表就自動失效.

　　四、主庫導入到從庫

　　################主庫操作################
　　[root@Master-Mysql tmp]# ll
　　-rw-r--r--. 1 root root 162236 Jul 8 21:30 all.sql.gz
　　[root@Master-Mysql tmp]# gzip -d all.sql.gz
　　[root@Master-Mysql tmp]# ll
　　-rw-r--r--. 1 root root 590351 Jul 8 21:30 all.sql
　　################主庫完成################
　　##備注：將主庫導出的all.sql通過scp、ssh、sftp等方式拷貝到從庫服務器，此處略##
　　[root@Slave-Mysql ~]# grep log-bin /etc/my.cnf
　　#log-bin = /usr/local/mysql/data/mysql-bin
　　[root@Slave-Mysql ~]# grep server-id /etc/my.cnf
　　server-id = 2
　　[root@Slave-Mysql ~]# /etc/init.d/mysqld restart
　　[root@Slave-Mysql tmp]# /usr/local/mysql/bin/mysql -uroot -p'admin' </tmp/all.sql
　　Warning: Using a password on the command line interface can be insecure.
　　[root@Slave-Mysql tmp]# /usr/local/mysql/bin/mysql -uroot -p'admin'
　　mysql> use hitest;
　　mysql> select * from test;
　　+----+------+
　　| id | name |
　　+----+------+
　　| 1 | zy   |
　　+----+------+
　　1 row in set (0.00 sec)
　　六、從庫配置信息

　　mysql> CHANGE MASTER TO
　　    -> MASTER_HOST='192.168.199.177',
　　    -> MASTER_PORT=3306,
　　    -> MASTER_USER='byrd',
　　    -> MASTER_PASSWORD='admin',
　　    -> MASTER_LOG_FILE='mysql-bin.000004',
　　    -> MASTER_LOG_POS=1305;
　　Query OK, 0 rows affected, 2 warnings (1.96 sec)
　　[root@Slave-Mysql ~]# ll /usr/local/mysql/data/master.info
　　##備注：master.info記錄MASTER的相關信息！
　　七、啟動從庫同步

　　mysql> start slave;
　　mysql> show slave status＼G
　　            Slave_IO_Running: Yes
　　            Slave_SQL_Running: Yes
　　            Seconds_Behind_Master: 0
　　八、結果測試

　　mysql> use hitest;
　　mysql> select * from test;
　　+----+--------+
　　| id | name   |
　　+----+--------+
　　| 1 | zy     |
　　| 2 | binghe |
　　+----+--------+
　　2 rows in set (0.00 sec)
　　[root@Master-Mysql ~]# /usr/local/mysql/bin/mysql -uroot -p'' -e "create database zhihu;"    #主庫建立了一個zhihu的數據庫
　　Enter password:
　　[root@Slave-Mysql ~]# /usr/local/mysql/bin/mysql -uroot -p'' -e "show databases like 'zhihu'";
　　Enter password:
　　+------------------+
　　| Database (zhihu) |
　　+------------------+
　　| zhihu            |
　　+------------------+
　　配置Mysql數據庫的主從同步(雙主)

　　已經配置好的:

　　主庫:192.168.199.177

　　從庫:192.168.199.178

　　[root@Master-Mysql ~]# egrep "server-id|log-slave|log-bin|auto_increment|slave-skip-errors" /etc/my.cnf
　　log-bin = /usr/local/mysql/data/mysql-bin    #必須
　　server-id = 1    #必須
　　log-slave-updates    #必須
　　auto_increment_increment = 2    #必須
　　auto_increment_offset = 1    #必須
　　slave-skip-errors = 1032,1062,1007    #非必須，建議
　　########################主庫、從庫分隔符########################
　　[root@Slave-Mysql data]# egrep "server-id|log-slave|log-bin|auto_increment|slave-skip-errors|read-only" /etc/my.cnf
　　#log-bin = /usr/local/mysql/data/mysql-bin
　　server-id = 2
　　log-slave-updates
　　log-bin = /usr/local/mysql/data/mysql-bin
　　#read-only    #雙主，此選項要注釋掉
　　slave-skip-errors = 1032,1062,1007
　　auto_increment_increment = 2    #ID自增間隔
　　auto_increment_offset = 2    #ID初始位置
　　192.168.199.178:
　　mysql> stop slave;
　　mysql> flush table with read lock;
　　mysql> show master status;
　　+------------------+----------+--------------+------------------+-------------------+
　　| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
　　+------------------+----------+--------------+------------------+-------------------+
　　| mysql-bin.000004 |      120 |              |                  |                   |
　　+------------------+----------+--------------+------------------+-------------------+
　　mysql> system /usr/local/mysql/bin/mysqldump -uroot -p'' -A -B >/tmp/192.168.199.178.sql   #如果主、從一致非必須
　　mysql> unlock tables;    #同上
　　mysql> system ls -l /tmp/
　　-rw-r--r--. 1 root root   2887406 Jul 12 22:24 192.168.199.178.sql
　　mysql> start slave;
　　192.168.199.177:
　　[root@Master-Mysql ~]# /usr/local/mysql/bin/mysql -uroot -p'' < /tmp/192.168.199.178.sql    #如果主、從一致非必須
　　mysql> update mysql.user set password=PASSWORD('admin') where user='root';
　　[root@Master-Mysql ~]# cat |/usr/local/mysql/bin/mysql -uroot -p'admin' <<EOF    #必須
　　> CHANGE MASTER TO
　　> MASTER_HOST='192.168.199.178',
　　> MASTER_PORT=3306,
　　> MASTER_USER='byrd',
　　> MASTER_PASSWORD='admin',
　　> MASTER_LOG_FILE='mysql-bin.000004',
　　> MASTER_LOG_POS=120;
　　> EOF
　　mysql> start slave;
　　mysql> show slave status＼G
　　*************************** 1. row ***************************
　　               Slave_IO_State: Waiting for master to send event
　　                  Master_Host: 192.168.199.178
　　                  Master_User: byrd
　　                  Master_Port: 3306
　　                Connect_Retry: 60
　　              Master_Log_File: mysql-bin.000004
　　          Read_Master_Log_Pos: 938
　　               Relay_Log_File: mysqld-relay-bin.000002
　　                Relay_Log_Pos: 1101
　　        Relay_Master_Log_File: mysql-bin.000004
　　             Slave_IO_Running: Yes
　　            Slave_SQL_Running: Yes
　　                   Last_Errno: 0
　　                 Skip_Counter: 0
　　          Exec_Master_Log_Pos: 938
　　              Relay_Log_Space: 1275
　　              Until_Condition: None
　　                Until_Log_Pos: 0
　　           Master_SSL_Allowed: No
　　        Seconds_Behind_Master: 0
　　Master_SSL_Verify_Server_Cert: No
　　                Last_IO_Errno: 0
　　               Last_SQL_Errno: 0
　　             Master_Server_Id: 2
　　                  Master_UUID: 34d672c3-d292-11e3-9ff5-00155dc7834c
　　             Master_Info_File: /usr/local/mysql/data/master.info
　　                    SQL_Delay: 0 --phpfensi.com
　　          SQL_Remaining_Delay: NULL
　　      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
　　           Master_Retry_Count: 86400
　　測試:192.168.199.177:

　　mysql> use hitest;
　　mysql> CREATE TABLE `ces` (
　　    -> `REL_ID` bigint(12) NOT NULL auto_increment COMMENT 'id',
　　    -> `TITLE` varchar(255) NOT NULL COMMENT 'biaoti',
　　    -> PRIMARY KEY (`REL_ID`)
　　    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
　　mysql> insert into ces(TITLE) values('test');
　　mysql> insert into ces(TITLE) values('test');
　　mysql> insert into ces(TITLE) values('test');
　　mysql> insert into ces(TITLE) values('test25');
　　mysql> select * from ces;
　　+--------+-------+
　　| REL_ID | TITLE |
　　+--------+-------+
　　|      1 | test |
　　|      3 | test |
　　|      5 | test |
　　|     25 | test25|
　　+--------+--------+
　　3 rows in set (0.03 sec)
　　192.168.199.178:
　　mysql> use hitest;
　　mysql> insert into ces(TITLE) values('test26');
　　mysql> insert into ces(TITLE) values('test28');
　　mysql> insert into ces(TITLE) values('test30');
　　mysql> select * from ces;
　　+--------+--------+
　　| REL_ID | TITLE |
　　+--------+--------+
　　|      1 | test   |
　　|      3 | test   |
　　|      5 | test   |
　　|     26 | test26 |
　　|     28 | test28 |
　　|     30 | test30 |
　　+--------+--------+
　　17 rows in set (0.00 sec)
　　說明:如果一主、一叢已經做好,只要知道從庫位置點(show master status;)、然后之前主庫執行(CHANGE MASTER)、之前主庫開啟slave(start slave)即可,其中數據庫備份等步驟可以省略,如果主從有一些數據庫不一致則同上操作.

（編輯：武林網）

上一篇：mysql采用命令行模式備份及還原數據

下一篇：Mysql從庫read-only配置詳情