数据库主从配置

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

数据库配置

主数据库：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;

参考文献
错误排查文献

**

**

微信

支付宝