配置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配置详情