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;

再看从服务器有没有同样的数据库表出现，有则是成功。