mysql学习总结09 — 优化(架构层)-主从复制与读写分离

$ vim /etc/my.conf
log-bin=mysql-bin #开启二进制日志文件
binlog_format=mixed #日志文件存储方式
server-id=1 #服务器识别id 
# 启动mysql服务
$ /usr/local/mysql/bin/mysqld_safe -user=mysql &

# 登录
$ /usr/local/mysql/bin/mysql -uroot -p
# 在主服务器创建同步账号 slave(123456) 以便从服务器同步主服务器数据
mysql> grant replication slave on *.* to 'slave'@'%' identified by '123456';
mysql> flush privileges;

mysql> grant all on *.* to 'api'@'%' identified by '123456';
mysql> flush privileges;

mysql> show master status;

$ vim /etc/my.conf
log-bin=mysql-bin #开启二进制日志文件
binlog_format=mixed #日志文件存储方式
server-id=2 #服务器识别id
# 启动mysql服务
$ /usr/local/mysql/bin/mysqld_safe -user=mysql &

# 登录
$ /usr/local/mysql/bin/mysql -uroot -p
mysql> grant all on *.* to 'api'@'%' identified by '123456';
mysql> flush privileges;

# 在从服务器将slave同步服务关闭
mysql> slave stop;
# 配置
mysql> change master to master_host="192.168.0.1", master_user="slave", master_password="123456", master_log_file="mysql-bin.000001", master_log_pos=100;
# 启动
mysql> slave start;

mysql> show slave status\G;

$con_write = mysql_connect('192.168.0.1','api','123456');
$con_read = mysql_connect('192.168.0.2','api','123456');