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;

再看從服務器有沒有同樣的數據庫表出現，有則是成功。