mysql数据库主从

1，A、B两台服务器安装好数据库，保持数据库版本一致，且服务器时间一致。

A[Master:192.168.2.10]

B[Slave:192.168.2.11]

2，进入数据库，检查是否可用

在centos7中，新安装的数据库有一个初始密码，执行命令grep "A temporary password" /var/log/mysqld.log即可获得

mysql> set PASSWORD = PASSWORD('n07|PcZJU6iJCKvh^6+m');   #重置密码

Mastar配置[192.168.2.10]

[[email protected] ~]# vim /etc/my.cnf

[mysqld]
server-id = 1
log_bin=master-bin
log_bin_index=master-bin.index
binlog_do_db=cookietest

[[email protected] ~]# systemctl restart mysqld

[[email protected] ~]# mysql -u root -p

mysql> grant replication slave on *.* to 'cookietest'@'192.168.2.11' identified by 'ELvIMD9mSpHfrl7XNn&&DA'; #从库权限配置

mysql> flush privileges;     #刷新

mysql> show master status;   #查看主库状态
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 | cookietest   |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
 

=========================================================================================

Slave配置[192.168.2.11]

[[email protected] ~]# vim /etc/my.cnf

[mysqld]
server-id = 2
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index

[[email protected] ~]# systemctl restart mysqld

mysql> change master to master_host='192.168.2.10',master_user='cookietest',master_password='ELvIMD9mSpHfrl7XNn&&DA',master_log_file='master-bin.000001',master_log_pos=154;

mysql> start slave;
mysql> show slave status \G;

 

=============================以下测试============================

Master：主库创建数据库及数据表并创建数据：

mysql> create schema cookietest;

mysql> use cookietest;

mysql> create table names(id INT(9),name VARCHAR(30));

mysql> insert into names(id,name) values(1,'cookie');

Slave：从库查看结果

大功告成！！！！！！！！！！！！！！