配置mysql的主从模式,当一台mysql出现问题后,另外一台可以正常工作。 两台机器IP:192.168.1.60(master)、192.168.1.61(slave) 注意:在配置之前,确保两台节点都有需同步的数据库。
192.168.1.60:master节点 vim /etc/my.cnf 添加如下内容:
[mysqld]
log-error=/data/mysql_log/error.log # mysql的error日志
log=/data/mysql_log/mysql.log # mysql的操作日志
log-bin=/data/mysql_log/binlog # 配置binlog,通过binlog将两个mysql同步
server-id=1 # 配置本台机器mysql的id
binlog-do-db = mytest # 配置需要同步的数据库
mysql>GRANT REPLICATION SLAVE ON *.* to 'root'@'192.168.1.61' identified by 'test123456';
mysql>FLUSH PRIVILEGES;
如果要允许所有主机访问,用%代替ip即可:
mysql>GRANT REPLICATION SLAVE ON *.* to 'root'@'%' identified by 'test123456';
mysql>FLUSH PRIVILEGES;
service mysqld restart 重启服务的过程中可能遇到启动失败的情况:
/usr/local/mysql/bin/mysqld: File '/data/mysql_log/binlog.index' not found (Errcode: 13)
这个主要是因为这个目录mysql用户没有权限导致的。 chown mysql /data/mysql_log 即可解决问题。
MySQL [mytest]> show master status;
+---------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| binlog.000004 | 106 | mytest | |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)
192.168.1.61:slave节点
vim /etc/my.cnf 添加如下内容:
[mysqld]
log-error=/data/mysql_log/error.log # mysql的error日志
log=/data/mysql_log/mysql.log # mysql的操作日志
server-id=2 # 配置本台机器mysql的id
master-host=192.168.1.60 # master节点的ip
master-user=root # 访问master节点的用户名
master-password=test123456 # 访问master节点的密码
master-port=3306 # 访问master节点的端口
replicate-do-db=mytest # 配置需要同步的数据库
mysql> slave start;
Query OK, 0 rows affected (0.04 sec)
MySQL [mytest]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 100.110.5.77
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000004
Read_Master_Log_Pos: 106
Relay_Log_File: TENCENT64-relay-bin.000014
Relay_Log_Pos: 248
Relay_Master_Log_File: binlog.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: mytest
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
看到Slave_IO_Running: Yes 和 Slave_SQL_Running: Yes ,就说明配置成功。
master 插入一个表:
MySQL [mytest]> CREATE TABLE IF NOT EXISTS `runoob_tbl`(
-> `runoob_id` INT UNSIGNED AUTO_INCREMENT,
-> `runoob_title` VARCHAR(100) NOT NULL,
-> `runoob_author` VARCHAR(40) NOT NULL,
-> `submission_date` DATE,
-> PRIMARY KEY ( `runoob_id` )
-> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
slave 查看效果:
MySQL [mytest]> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| runoob_tbl |
+------------------+
1 row in set (0.00 sec)
master 插入数据:
MySQL [mytest]> insert into runoob_tbl(runoob_title,runoob_author,submission_date) values("ad","bb",now());
Query OK, 1 row affected, 1 warning (0.00 sec)
MySQL [mytest]> insert into runoob_tbl(runoob_title,runoob_author,submission_date) values("ae","bb",now());
Query OK, 1 row affected, 1 warning (0.00 sec)
MySQL [mytest]> insert into runoob_tbl(runoob_title,runoob_author,submission_date) values("af","bb",now());
Query OK, 1 row affected, 1 warning (0.00 sec)
slave查看数据:
MySQL [mytest]> select * from runoob_tbl;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
| 1 | ad | bb | 2018-03-08 |
| 2 | ae | bb | 2018-03-08 |
| 3 | af | bb | 2018-03-08 |
+-----------+--------------+---------------+-----------------+
3 rows in set (0.00 sec)
以上则说明配置测试成功。
mysql>flush tables with read lock;
mysqldump -uroot -ptest123 cmdb > cmdb.sql
scp cmdb.sql root@192.168.8.11:/root/ 4、主库解锁
mysql>unlock tables;
1)停止从库slave
mysql>slave stop;
2)新建数据库cmdb
mysql> create database cmdb default charset utf8;
3)导入数据
mysql -uroot -ptest123 cmdb < cmdb.sql
4)查看从库已有该数据库和数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cmdb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 启动slave进程
mysql>slave start;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.8.10',
MASTER_PORT=3306,
MASTER_USER='rep1',
MASTER_PASSWORD='test123456',
MASTER_LOG_FILE='binlog.000001',
MASTER_LOG_POS=1304; #后面两个参数的值与主库保持一致