PHP设置数据库主从的配置,数据库主从配置

数据库配置

主数据库：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 STATUS\G # 查看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;

**

给程序员一个鼓励呗！

**

微信

支付宝