Mysql 主从搭建
Mysql 主从搭建
操作系统：
[root@localhost ~]# cat /etc/redhat-release
CentOS release 6.8 (Final)
Master(主)：192.168.137.32
Slave (从) ：192.168.137.33
第一步：在CentOS6.x下安装MySQL数据库
这里我直接用脚本安装mysql5.7的
vim auto_install_mysql.sh
#!/bin/bash
yum install -y wget
wget https://dev.mysql.com/get/mysql57-community-release-el6-9.noarch.rpm
rpm -Uvh mysql57-community-release-el6-9.noarch.rpm
yum clean all
sleep 2
yum install mysql-community-server -y
/etc/init.d/mysqld start
chkconfig --level 2345 mysqld on
for i in `grep 'temporary password' /var/log/mysqld.log| awk -F": " '{print $2}'`;
do
mysql -uroot -p$i -e "set global validate_password_policy=0;" -b --connect-expired-password
mysql -uroot -p$i -e "set global validate_password_length=6;" -b --connect-expired-password
mysql -uroot -p$i -e "ALTER USER 'root'@'localhost' IDENTIFIED BY 'abc123';" -b --connect-expired-password
/usr/bin/mysqladmin -u root -p$i password 'abc123' -b --connect-expired-password
mysql -uroot -p"abc123" -e "show databases;"
echo "mysql is install ok"
done
service mysqld restart
iptables -A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
service iptables save
/etc/init.d/iptables restart
#注意：mysql5.7里面有安全机制，新装的数据库有随机密码在/var/log/mysql.log
为了方便操作 我把密码修改abc123
第二步：将数据库修改为外部ip可以访问
Mysql -uroot -p 回车输入密码abc123进行登录数据库
你想mysql账户myuser使用密码mypassword从任何主机连接到mysql服务器的话，那就在mysql命令行下输入：
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'abc123' WITH GRANT OPTION;
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
#当报当前设置的密码不满足密码策略，以下进行解决
mysql> set global validate_password_policy=0; #设置密码复杂度为0，就是低
Query OK, 0 rows affected (0.14 sec)
mysql> set global validate_password_length=6; #设置密码长度为6
Query OK, 0 rows affected (0.02 sec)
mysql> grant all privileges on *.* to 'root'@'%' identified by 'abc123' with grant option;
Query OK, 0 rows affected, 1 warning (0.29 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.41 sec)
mysql> quit
Bye
然后 flush privileges 刷新权限 输入quit 保存退出
Master(192.168.137.32):
新加如下俩行
vim /etc/my.cnf
server-id=1
log-bin=mysql-bin
Slave(192.168.137.33):
vim /etc/my.cnf
server-id=2
log-bin=mysql-bin
server-id唯一，每台机子自取ID名称 master: service_id=1 slave:server_id=2
1) 接下来确认slave和master的上的server_id是否正确。可以分别在slave和master的mysql上运行 SHOW VARIABLES LIKE'server_id'; 来查看server_id是否和你配置的一样
Master:
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+
1 row in set (0.12 sec)
Slave:
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 2 |
+---------------+-------+
1 row in set (0.11 sec)
2) 分别重新启动master,slaver的二台mysql服务 service mysqld restart
3) 登陆 mysql -u root -p 回车
4) 输入abc123 密码
5) master输入
6) Mysql> show master status
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
7) 记录下 FILE 及 Position 的值，在后面进行从服务器操作的时候需要用到。
8) 配置slave服务器的MYSQL内输入
mysql> change master to
-> master_host='192.168.137.32',
-> master_user='root',
-> master_password='abc123',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.13 sec)
9) 正确执行后启动Slave同步进程
10) 启动slave 查看slave状态
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.137.32
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 531
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: d7780cea-92ec-11e7-b97b-000c29e62b50
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
11) 其中红色标注Slave_IO_Running 与 Slave_SQL_Running 的值都必须为YES，才表明状态正常。
测试主从复制
1:先确定主，从库上没有任何自定义表
2：主服务器上的操作
在master主服务器上创建数据库
Create database mydb2;
在从服务器上
Show databases; 看看没有没有出现mydb2
show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: No
有问题了，Slave_SQL_Running应该是Yes才对。
再往下看，有错误的提示：
Last_Errno: 1053
Last_Error: Query partially completed on the master (error on master: 1053) and was aborted. There is a chance that your master is inconsistent at this point. If you are sure that your master is ok, run this query manually on the slave and then restart the slave with SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; . Query: 'INSERT INTO hx_stat_record ......(一句SQL语句)'
这里有说明要怎么操作了：）
先stop slave，然后执行了一下提示的语句，再SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
start slave;
show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果还是不行，先关闭slave，mysql>stop slave,接着重新配置偏移量，从主库：show master status; 接着从库mysql内，输入：
change master to
master_host='192.168.137.32',
master_user='root',
master_password='abc123',
master_log_file='mysql-bin.000001',
master_log_pos=154;
再看从服务器有没有同样的数据库表出现，有则是成功。