一、准备工作:
1.主从数据库版本最好一致
2.主从数据库内数据保持一致
主数据库:148.70.10.168 /linux
从数据库:47.98.113.189 /linux
二、主数据库master修改:
1.修改mysql配置
找到主数据库的配置文件my.cnf(或者my.ini),我的在/etc/mysql/my.cnf,在[mysqld]部分插入如下两行:
[mysqld] log-bin=mysql-bin #开启二进制日志 server-id=111 #设置server-id
2.重启mysql,创建用于同步的用户账号
service mysqld restart
打开mysql会话shell>mysql -h127.0.0.1 -uroot -ppassword
创建用户并授权:用户:slave密码:slavepass
mysql> CREATE USER 'slave'@'47.98.113.189' IDENTIFIED BY 'slavepass';#创建用户 mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'47.98.113.189';#分配权限 mysql>flush privileges; #刷新权限
3.查看master状态,记录二进制文件名(mysql-bin.000003)和位置(73):
mysql > SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 73 | test | manual,mysql | +------------------+----------+--------------+------------------+
三、从服务器slave修改:
1.修改mysql配置
同样找到my.cnf配置文件,添加server-id
[mysqld] server-id=222 #设置server-id,必须唯一
2.重启mysql,打开mysql会话,执行同步SQL语句(需要主服务器主机名,登陆凭据,二进制文件的名称和位置):
mysql> CHANGE MASTER TO -> MASTER_HOST='148.70.10.168', -> MASTER_USER='slave', -> MASTER_PASSWORD='slavepass', -> MASTER_LOG_FILE='mysql-bin.000003', -> MASTER_LOG_POS=73;
3.启动slave同步进程:
mysql>start slave;
4.查看slave状态:
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 182.92.172.80 Master_User: rep1 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000013 Read_Master_Log_Pos: 11662 Relay_Log_File: mysqld-relay-bin.000022 Relay_Log_Pos: 11765 Relay_Master_Log_File: mysql-bin.000013 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: ...
注:Slave_IO及Slave_SQL进程必须正常运行,即YES状态,否则都是错误的状态(如:其中一个NO均属错误)。
以上操作过程,主从服务器配置完成。
四、主从服务器测试:
主服务器Mysql,建立数据库,并在这个库中建表插入一条数据:
mysql> create database hi_db;
Query OK, 1 row affected (0.00 sec)
mysql> use hi_db;
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 |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
从服务器Mysql查询:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hi_db | //I'M here,大家看到了吧
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> use hi_db
Database changed
mysql> select * from hi_tb; //查看主服务器上新增的具体数据
+------+------+
| id | name |
+------+------+
| 1 | bobu |
+------+------+
1 row in set (0.00 sec)
完成:
编写一shell脚本,用nagios监控slave的两个yes(Slave_IO及Slave_SQL进程),如发现只有一个或零个yes,就表明主从有问题了,发短信警报吧。