安装两台CentOS+mysql
mysql版本号:
5.1.73(mysql Ver 14.14 Distrib 5.1.73)操作系统版本号:
CentOS release 6.5
安装过程略。
IP 地址:
192.168.1.16(主节点)
192.168.1.12(从节点)
备选命令:
启动mysql服务
service mysqld start
停止mysql服务
service mysqld stop
停止防火墙
service iptables stop
配置步骤:
1.修改主节点配置:
vi /etc/my.cnf
2.增加两行红字内容:
[mysqld]
server-id=1
log-bin=mysql-bin
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
3.启动mysql命令行,执行sql
mysql >GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO backup@'192.168.1.12' IDENTIFIED BY '1234';
4.重启master
查看master状态
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 106 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)
5.修改从节点配置:
vi /etc/my.cnf
6.增加如下红字内容:
[mysqld]
log_bin = mysql-bin
server_id = 2
relay_log = mysql-relay-bin
log_slave_updates = 1
read_only = 1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
7.重启slave节点
8.在从节点,启用复制,注意用户名和密码,以及日志文件和日志位置必须对应于Master的配置:
命令: mysql -u root -p
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.16', MASTER_USER='backup', MASTER_PASSWORD='1234' ,MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=106;
mysql> start slave;
9.测试主从复制内容:
主服务器Mysql,建立数据库,并在这个库中建表插入一条数据:
mysql> create database hi_db2;
Query OK, 1 row affected (0.00 sec)
mysql> use hi_db2;
Database changed
mysql> create table hi_tb(id int(3),name char(10));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into hi_tb values(001,'bobu');
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hi_db |
| hi_db2 |
| mysql |
| test |
+--------------------+
5 rows in set (0.00 sec)
从服务器Mysql查询:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hi_db2 |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> use hi_db2
Database changed
mysql> select * from hi_tb;
+------+------+
| id | name |
+------+------+
| 1 | bobu |
+------+------+
1 row in set (0.00 sec)
11.细心的读者可能发现主从并没有完全同步,这是因为作者在做第一次同步的时候已经失败过一次。所以, hi_db没有同步到从数据(show slave status\G的状态中会有报错)。作者将两个数据库都关闭了,然后重新配置了一遍同步,才做成功。
这两个都为YES才是同步正常:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
另外一篇文章,关于配置读写分离的程序架构的文章: