资讯专栏INFORMATION COLUMN

数据库主从配置

U2FsdGVkX1x / 1440人阅读

摘要:注意版本之后,已经不支持把配置属性写入配置文件中了,只需要把同步的数据库和要忽略的数据库写入即可。

数据库配置

主数据库:172.20.17.211 
从数据库:172.20.17.210

一、创建主/从数据同步账户( 主从两台数据库都要执行 )

# 开启远程访问支持
/usr/local/mysql/bin/mysql -h172.20.17.211 -uroot -pwoshishui         #登录数据库 
update mysql.user set Host="%" where User="root" and Host="localhost";  #开启Mysql远程访问支持
select Host,User,Password from mysql.user limit 10 G;                    # 查看修改结果
flush privileges;                                                         # 刷新权限
# 主/从数据库添加授权账户
CREATE USER "xzdesk"@"%" IDENTIFIED BY "woshishui";                        # 创建授权账户xzdesk 密码woshishui
GRANT ALL ON *.* TO "xzdesk"@"127.0.0.1";                                  # 授权xzdesk用户拥有全部数据库的所有权限
GRANT ALL ON `ceshi`.* TO "xzdesk"@"%";                                    # 授权xzdesk用户拥有ceshi数据库的所有权限

# 第二种 授权和添加账户一起操作
# GRANT ALL ON `ceshi`.* TO "xzdesk"@"127.0.0.1" IDENTIFIED BY "woshishui";

# 撤销用户授权
REVOKE ALL ON *.* TO "xzdesk"@"%";                                        # 撤销xzdesk的所有授权
# REVOKE delete ON databasename.tablename FROM "username"@"host";         # 没搞明白撒意思,不用执行
# 创建授权用户
#insert into mysql.user(Host,User,Password) values("172.20.17.210","xzdesk",password("woshishui"));
# 删除xzdesk用户( 授权信息无法删除 )
#DELETE FROM user WHERE User="xzdesk" and Host="%";

二、主服务器配置(172.20.17.211)

/usr/local/mysql/bin/mysql -h172.20.17.211 -uroot -pwoshishui
create database ceshi;                                                    # 创建ceshi数据库
flush tables with read lock;                                              # 数据库只读锁定命令
# 导出主服务器数据库
/usr/local/mysql/bin/mysqldump -h172.20.17.211 -uroot -pwoshishui ceshi > /data/ceshi_20160609.sql
unlock tables;                                                            # 解除锁定


# 主服务器数据导入从服务器
    # 第一种方法
    /usr/local/mysql/bin/mysql -h172.20.17.210 -uroot -pwoshishui         # 进入从服务器MySQL控制台
    create database ceshi;                # 创建数据库
    use ceshi                             # 进入数据库
    /usr/local/mysql/bin/mysql -h172.20.17.210 -uroot -pwoshishui ceshi < /data/ceshi_20160609.sql

    # 第二种方法
    /usr/local/mysql/bin/mysql -h172.20.17.210 -uroot -pwoshishui         # 进入从服务器MySQL控制台
    create database ceshi;                                                # 创建数据库
    use ceshi                                                             # 进入数据库
    source /data/ceshi_20160609.sql                                       # 导入备份文件到数据库

四、配置MySQL主服务器的my.cnf文件

vim /etc/my.cnf               # 编辑配置文件,在[mysqld]部分添加下面内容
server-id=1                   # 设置服务器id,为1表示主服务器,注意:如果原来的配置文件中已经有这一行,就不用再添加了。
log_bin=mysql-bin             # 启动MySQ二进制日志系统,注意:如果原来的配置文件中已经有这一行,就不用再添加了。
binlog-do-db=ceshi            # 需要同步的数据库名,如果有多个数据库,可重复此参数,每个数据库一行
binlog-ignore-db=mysql        # 不同步mysql系统数据库
binlog-ignore-db=performance_schema
binlog-ignore-db=information_schema
:wq!    #保存退出

service mysqld  restart      # 重启MySQL
mysql -u root -p             # 进入mysql控制台
show master status;          # 查看主服务器,出现以下类似信息
+------------------+----------+--------------+---------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            |
+------------------+----------+--------------+---------------------------------------------+
| mysql-bin.000006 |      575 | ceshi        | mysql,performance_schema,information_schema |
+------------------+----------+--------------+---------------------------------------------+

五、配置MySQL从服务器的my.cnf文件

vim /etc/my.cnf                       # 编辑配置文件,在[mysqld]部分添加下面内容
server-id=2                           # 配置文件中已经有一行server-id=1,修改其值为2,表示为从数据库
log-bin=mysql-bin                     # 启动MySQ二进制日志系统,注意:如果原来的配置文件中已经有这一行,就不用再添加了。
replicate-do-db=ceshi                 # 需要同步的数据库名,如果有多个数据库,可重复此参数,每个数据库一行
replicate-ignore-db=mysql             # 不同步mysql系统数据库
replicate-ignore-db=performance_schema
replicate-ignore-db=information_schema
:wq!    #保存退出

//CHANGE MASTER TO MASTER_HOST="172.20.17.110", MASTER_USER="slave1", MASTER_PASSWORD="123456", MASTER_LOG_FILE="mysql-bin.000054", MASTER_LOG_POS=2693;
service mysqld restart               # 重启MySQL
/usr/local/mysql/bin/mysql -h172.20.17.210 -uroot -pwoshishui
slave stop;                          # 停止slave同步进程
change master to master_host="172.20.17.211",master_user="xzdesk",master_password="woshishui",master_log_file="mysql-bin.000006" ,master_log_pos=575;    #执行同步语句
slave start;                         # 开启slave同步进程
SHOW SLAVE STATUSG                  # 查看slave同步信息,出现以下内容

               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.20.17.211
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 341
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 487
        Relay_Master_Log_File: mysql-bin.000006
              Slave_IO_Running: Yes         # 此状态必须YES
            Slave_SQL_Running: Yes         # 此状态必须YES
              Replicate_Do_DB: ceshi
          Replicate_Ignore_DB: mysql,performance_schema,information_schema

注意:Slave_IO及Slave_SQL进程必须正常运行,即YES状态,否则都是错误的状态(如:其中一个NO均属错误)。
注意:MySQL 5.1.7版本之后,已经不支持把master配置属性写入my.cnf配置文件中了,只需要把同步的数据库和要忽略的数据库写入即可。
# mysql -uroot -p
mysql> show global variables like "%read_only%";
mysql> flush tables with read lock;
mysql> set global read_only=1;
mysql> show global variables like "%read_only%";


将MySQL从只读设置为读写状态的命令:
mysql> unlock tables;
mysql> set global read_only=0;


对于需要保证master-slave主从同步的salve库,如果要设置为只读状态,需要执行的命令为:
mysql> set global read_only=1;


将salve库从只读状态变为读写状态,需要执行的命令是:
mysql> set global read_only=0;

连续输入两条命令确保从数据库只读:

set global read_only=1;
flush tables with read lock;

参考文献
错误排查文献

**

给程序员一个鼓励呗!

**

微信

支付宝

文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。

转载请注明本文地址:https://www.ucloud.cn/yun/30372.html

相关文章

  • Mysql二进制日志文件(binlog)主从复制最佳实践

    摘要:的主从备份相关配置服务器号,不要和其他服务器重复开启二进制日志索引二进制日志的文件名设为就是把每次发生的修改和事件的日志即时同步到硬盘上复制模式防止从服务器在崩溃后自动开启,以给你足够的时间修复。 实践背景 最近加入了同学的技术分享小组,4个人分两组,半月进行一次技术分享,现在一起搞Mysql我被分到讲解Mysql日志方面,上周已经讲完了,不过他们总是觉得对于日志这块了解不透彻,觉得不...

    刘永祥 评论0 收藏0
  • Mysql二进制日志文件(binlog)主从复制最佳实践

    摘要:的主从备份相关配置服务器号,不要和其他服务器重复开启二进制日志索引二进制日志的文件名设为就是把每次发生的修改和事件的日志即时同步到硬盘上复制模式防止从服务器在崩溃后自动开启,以给你足够的时间修复。 实践背景 最近加入了同学的技术分享小组,4个人分两组,半月进行一次技术分享,现在一起搞Mysql我被分到讲解Mysql日志方面,上周已经讲完了,不过他们总是觉得对于日志这块了解不透彻,觉得不...

    YFan 评论0 收藏0
  • Redis主从复制以及主从复制原理

    摘要:前者称为主节点,后者称为从节点数据的复制是单向的,只能由主节点到从节点。主从复制的作用数据冗余主从复制实现了数据的热备份,是持久化之外的一种数据冗余方式。检测主节点当前是否可接受处理命令。 showImg(https://segmentfault.com/img/bVboOAF?w=1772&h=591); Redis是一个开源的使用ANSI C语言编写、支持网络、可基于内存亦可持久化...

    snowLu 评论0 收藏0
  • MySQL5.7 主从复制配置

    摘要:每一个主从复制的连接,都有三个线程。上创建一个测试数据库四主从复制创建授权用户创建用户,指定该用户只能在主库上使用密码登录为用户赋予权限。 一、主从复制原理 MySQL 主从复制是一个异步的复制过程,主库发送更新事件到从库,从库读取更新记录,并执行更新记录,使得从库的内容与主库保持一致。每一个主从复制的连接,都有三个线程。拥有多个从库的主库为每一个连接到主库的从库创建一个 log du...

    wenhai.he 评论0 收藏0
  • MySQL5.7 主从复制配置

    摘要:每一个主从复制的连接,都有三个线程。上创建一个测试数据库四主从复制创建授权用户创建用户,指定该用户只能在主库上使用密码登录为用户赋予权限。 一、主从复制原理 MySQL 主从复制是一个异步的复制过程,主库发送更新事件到从库,从库读取更新记录,并执行更新记录,使得从库的内容与主库保持一致。每一个主从复制的连接,都有三个线程。拥有多个从库的主库为每一个连接到主库的从库创建一个 log du...

    rainyang 评论0 收藏0
  • redis系列:主从复制

    摘要:简介这篇文章主要讲述的主从复制功能。从服务器在身份验证时可能遇上的情况如下发送端口信息身份验证通过后,从服务器会向主服务器发送自己的监听端口号。主服务器收到之后会将端口号记录到从服务器对应的状态属性中。的主从复制功能就介绍到这里了。 1 简介 这篇文章主要讲述Redis的主从复制功能。会依次从环境搭建、功能测试和原理分析几个方面进行介绍。 2 准备工作 服务器架构图如下 showIm...

    赵春朋 评论0 收藏0

发表评论

0条评论

最新活动
阅读需要支付1元查看
<