openstack云主机主从复制

环境准备:

vm1(主)

测试连通性

echo "nameserver 8.8.8.8" >> /etc/resolv.conf 

[root@vm1 opt]# ping baidu.com
PING baidu.com (220.181.38.251) 56(84) bytes of data.
64 bytes from 220.181.38.251: icmp_seq=1 ttl=127 time=42.9 ms
64 bytes from 220.181.38.251: icmp_seq=2 ttl=127 time=42.2 ms
^C
--- baidu.com ping statistics ---
2 packets transmitted, 2 received, 0% packet loss, time 1006ms
rtt min/avg/max/mdev = 42.294/42.616/42.938/0.322 ms

安装mariadb

yum install mariadb mariadb-server -y
systemctl restart mariadb

修改配置文件

vi /etc/my.cnf

[mysqld]
server-id=1(添加)
log-bin=mysql-bin (添加)

重启mariadb

systemctl restart mariadb

初始化

mysql_secure_installation

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!

登录数据库

 mysql -uroot -p 123456

在mysql中授权

grant all on *.* to root@'%' identified by '123456';
flush privileges;
show master status;

记录下File和Position的值，到这里就不要操作主Mysql，防止服务器状态值变化，因为mysql主从复制是从主数据库当前状态开始复制
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |     1767 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)

vm2(从)

安装mariadb

yum -y install mariadb mariadb-server
systemctl start mariadb

修改配置文件

[mysqld]
server-id=2
relay-log=mysql-relay  #启用中继日志

重启mariadb

systemctl start mariadb

初始化

mysql_secure_installation

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!

登录数据库

mysql -uroot -p 123456

配置主从关系

change master to master_host='192.168.10.15',
master_user='root',
master_password='123456',
master_log_file='mysql-bin.000001',
master_log_pos=1767;

启动服务

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)

查看从服务器状态

show slave status\G;

测试:
vm1

vm1创建表

MariaDB [ChinaSkilldb]> create table testtable(id int not null primary key,Tramname varchar(50),remarks varchar(255));
Query OK, 0 rows affected (0.07 sec)

查看表

MariaDB [ChinaSkilldb]> show create table testtable;
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                  |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| testtable | CREATE TABLE `testtable` (
  `id` int(11) NOT NULL,
  `Tramname` varchar(50) DEFAULT NULL,
  `remarks` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

查看表结构

MariaDB [ChinaSkilldb]> desc testtable;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int(11)      | NO   | PRI | NULL    |       |
| Tramname | varchar(50)  | YES  |     | NULL    |       |
| remarks  | varchar(255) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

插入数据

MariaDB [ChinaSkilldb]> insert into testtable values(1,"Cloud","ChinaSkill");
Query OK, 1 row affected (0.04 sec)

查看插入数据

MariaDB [ChinaSkilldb]> select * from testtable;
+----+----------+------------+
| id | Tramname | remarks    |
+----+----------+------------+
|  1 | Cloud    | ChinaSkill |
+----+----------+------------+
1 row in set (0.00 sec)

vm2 查看

至此云主机主从复制到此结束