配置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)
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
          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）即可。其中数据库备份等步骤可以省略，如果主从有一些数据库不一致则同上操作！

时间： 2024-11-03 21:42:03

配置Mysql数据库的主从同步教程的相关文章

详解MySQL数据库设置主从同步的方法_Mysql

简介 MySQL主从同步是目前使用比较广泛的数据库架构,技术比较成熟,配置也不复杂,特别是对于负载比较大的网站,主从同步能够有效缓解数据库读写的压力. MySQL主从同步的机制: MySQL同步的流程大致如下:      1.主服务器(master)将变更事件(更新.删除.表结构改变等等)写入二进制日志(master log).      2.从服务器(slave)的IO线程从主服务器(binlog dump线程)获取二进制日志,并在本地保存一份自己的二进制日志(relay log)     

MySQL数据库的主从同步实现及应用

主从同步机制及应用 读写分离(Read/Write Splitting)让主数据库处理事务性增.改.删操作(INSERT.UPDATE.DELETE),从数据库处理SELECT查询操作, 在大规模高并发的系统中,读写分离是提高性能很重要的手段. 读写分离实现的基础就是MySQL的主从同步,因此对于主从同步的管理也就显得非常重要. (1)MYSQL主从同步的作用 1.数据分布 2.负载平衡(load balancing) 3.备份(Copy) 4.高可用性(high availability)和容

PHP开发环境配置(MySQL数据库安装图文教程)_php技巧

一. MySQL的安装 运行MYSQL安装程序(mysql-essential-5.1.40-win32.msi) 开发环境配置(MySQL数据库安装图文教程)_php技巧-mysql数据库主从配置">   选择安装类型为Custom   点选Change按钮更改安装目录   将安装目录更改为到D盘(可根据自己的系统更改)     点击Install按钮开始安装   安装程序将开始安装MySQL到指定的路径中     安装过程中汇出现一些广告点Next跳过即可.     安装完成后出现以下

基于 CentOS Mysql 安装与主从同步配置详解

CentOS Mysql 安装 Mysql (Master/Slave) 主从同步 1.为什么要使用主从同步 1.如果主服务器出现问题,可以快速切换到从服务器提供的服务 2.可以在从服务器上执行查询操作,降低主服务器的访问压力 3.可以在从服务器上执行备份,以避免备份期间影响主服务器的服务 注意:一般只有更新不频繁的数据或者对实时性要求不高的数据可以通过从服务器查询,实时性要求高的数据仍然需要从主服务器获得. Window 数据库主从(Master/Slave)同步安装与配置详解 2. Cent

ubuntu下简单配置mysql数据库

ubuntu下简单配置mysql数据库 一). ubuntu下mysql安装布局: /usr/bin                      客户端程序和mysql_install_db /db                             数据库和日志文件 /var/run mysqld        服务器 /etc/mysql mysql       配置文件my.cnf /usr/share/mysql       字符集,基准程序和错误消息 /etc/init.d/mysq

Mysql Replication机制主从同步实践

 Mysql Replication机制主从同步实践 参考资料: Install mysql:https://www.linode.com/docs/databases/mysql/how-to-install-mysql-on-ubuntu-14-04replication mysql:https://dev.mysql.com/doc/refman/5.5/en/replication-howto.html (如果安装mysql过程中,有"media change: please ins

MySql主主(主从)同步配置详解

一.MySQL复制概述 MySQL支持单向.异步复制,复制过程中一个服务器充当主服务器,而一个或多个其它服务器充当从服务器.MySQL复制基于主服务器在二进制日志中跟踪所有对数据库的更改(更新.删除等等).因此,要进行复制,必须在主服务器上启用二进制日志.每个从服务器从主服务器接收主服务器上已经记录到其二进制日志的保存的更新.当一个从服务器连接主服务器时,它通知主服务器定位到从服务器在日志中读取的最后一次成功更新的位置.从服务器接收从那时起发生的任何更新,并在本机上执行相同的更新.然后封锁并等待

mysql数据库replication 主从(master-slave)同步

这篇文章如果能加上auto-increment-increment,auto-increment-offset,这二个配置会更好一点. 重新配置mysql replication,是为了研究MHA作准备. 一,服务器说明 192.168.10.103 master 192.168.10.209 slave1 192.168.10.219 slave2 一主二从 二,mysql配置 1,master配置 [mysqld]    server-id=1  log-bin=mysql-bin  rea

MySQL数据库的主从配置

    需要两台主机,分别担任主服务器和从服务器    主服务器IP:192.168.1.1    从服务器IP:192.168.1.2    首先分别在两台服务上配置mysql服务,见http://www.178-go.com/archives/76    在主服务器上:    # vim /etc/my.cnf    修改或添加如下条目    log-bin=master-bin    log-bin-index=master-bin.index    server-id=11    注:s