mysql主从的环境安装配置

第三方工具

yum -y install wget net-tools

下载mysql的repo源

wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
rpm -ivh mysql-community-release-el7-5.noarch.rpm
sudo yum install -y  mysql-server
sudo chown -R root:root /var/lib/mysql
systemctl restart mysql.service

初始化mysql

mysql -u root
#设置msyql密码为 123456
mysql> set password for 'root'@'localhost' =password('123456');
#远程连接设置，所有以root账号连接的远程用户，设其密码为 123456
mysql> grant all privileges on *.* to root@'%'identified by '123456';
#删除密码为空的用户
mysql> DELETE FROM mysql.user WHERE user='';
#更新权限
mysql>flush privileges;
mysql > exit;

配置

#设置开启日志 重要
vi /etc/my.cnf
#在mysqld节添加如下配置  第一行设置日志文件名，第二行设置服务器唯一id
log-bin=mysqlbin-log-bin
server-id=17
#重启mysql
systemctl restart mysql.service
#查看程序存储目录
cd /var/lib/mysql
ll | grep mysqlbin
#查看到以下为成功
-rw-rw----. 1 mysql mysql      120 3月   7 13:46 mysqlbin-log-bin.000001
-rw-rw----. 1 mysql mysql       26 3月   7 13:46 mysqlbin-log-bin.index

从服务器

#创建从服务器连接地址用户名slave
mysql -u root -p
#输入密码 设置专门帐号对应专门地址访问
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.37.85' IDENTIFIED BY '123456';
mysql> SHOW MASTER STATUS; //查看到当前的日志位置
+-------------------------+----------+--------------+------------------+-------------------+
| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------------+----------+--------------+------------------+-------------------+
| mysqlbin-log-bin.000001 |     2628 |              |                  |                   |
+-------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

#从服务器再次安装
yum -y install wget net-tools
#下载mysql的repo源
wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
rpm -ivh mysql-community-release-el7-5.noarch.rpm
sudo yum install -y  mysql-server
sudo chown -R root:root /var/lib/mysql
systemctl restart mysql.service
初入安装 root用户没有密码：
mysql -u root
#设置msyql密码为 123456
mysql> set password for 'root'@'localhost' =password('123456');
#远程连接设置，所有以root账号连接的远程用户，设其密码为 123456
mysql> grant all privileges on *.* to root@'%'identified by '123456';
#删除密码为空的用户
mysql> DELETE FROM mysql.user WHERE user='';
#更新权限
mysql>flush privileges;
mysql > exit;
vi /etc/my.cnf
#在mysqld节添加如下配置 设置服务器唯一id
server-id=18
#重启
systemctl restart mysql.service
mysql -u root -p
CHANGE MASTER TO master_host='192.168.37.57', master_user='slave',master_password='123456', master_log_file='mysqlbin-log-bin.000001', master_log_pos=2628;
START SLAVE;
SHOW SLAVE STATUS \G

测试配置情况

主服务器写
CREATE TABLE test.a (id INT, num INT);
INSERT INTO test.a VALUES(1, 2);
从服务器读
SELECT * FROM test.a \G
从服务器写
INSERT INTO test.a VALUES(3, 4);
主服务器读
SELECT * FROM test.a \G
主服务器删除测试数据
DROP TABLE test.a;
删除后从服务器中的a表也会自动删除

########关闭同步
stop slave;
reset slave all;
SHOW SLAVE STATUS \G