mysql 主從同步-讀寫分離

標籤：relay   backup   emctl   mysqld   字元   ted   檔案   art   roo   

一、       實驗環境

Master                   centos 7.3              192.168.138.13

Slave                     centos 7.3               192.168.138.14

二、在master操作

1. 安裝並設定檔

[[email protected] ~]# yum install mariadb-server -y

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

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

server-id=1                  //必須唯一

log-bin=mysql-bin          //開啟binlog日誌

character-set-server=utf8         //設定字元集

        2.啟動mysql

[[email protected] ~]# systemctl start mariadb

        3. 授權root使用者

MariaDB [(none)]> grant all on *.* to [email protected]‘localhost‘ identified by ‘123456‘;

MariaDB [(none)]> grant all on *.* to [email protected]‘%‘ identified by ‘123456‘;

         4． 重新整理許可權表

MariaDB [(none)]> flush privileges

5.查看master狀態

MariaDB [(none)]> show master status;

+------------------+----------+--------------+------------------+

| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000003 |    521 |                   |                  |

+------------------+----------+--------------+------------------+

           

注意：如果主庫中已存在資料，則需要備份拷到從庫保持資料一致性

1.鎖定資料表，避免在備份過程中，表被更新

mysql>LOCK TABLES tbl_name READ;

為表增加一個寫鎖定：

mysql>LOCK TABLES tbl_name WRITE;

解鎖 ： mysql>UNLOCK TABLES;

2.備份資料

方法一

Mysqldump  -uroot  -p –B db1 –T tb1  | gzip  > /mysqlbackup/tb1.sql.gz

方法二

直接備份datadir=/var/lib/mysql

                                                                                        

三、       在slave上操作

1. 安裝並配置/etc/my,cnf

[[email protected] ~]# yum install mariadb-server -y

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

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

server-id=1                  //必須唯一

character-set-server=utf8         //設定字元集

        2. 啟動mysql

[[email protected] ~]# systemctl start mariadb

3. 配置同步參數

MariaDB [(none)]> change master to

 master_host=‘192.168.138.13‘,

master_user=‘root‘,

master_password=‘123456‘,

master_port=3306,

master_log_file=‘mysql-bin.000003‘,

master_log_pos=521;

4. 啟動主從同步進程

          MariaDB [(none)]> start slave;

        5. 檢查狀態

MariaDB [(none)]> show slave status\G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.138.13

                  Master_User: root

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000003

          Read_Master_Log_Pos: 521

               Relay_Log_File: mariadb-relay-bin.000002

                Relay_Log_Pos: 529

        Relay_Master_Log_File: mysql-bin.000003

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

看到兩個yes說明配置成功

四、 測試

         1.在slave上查看

        MariaDB [(none)]> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

+--------------------+

3 rows in set (0.00 sec)

2. 在master建立資料庫

MariaDB [(none)]> create database testdb1;

Query OK, 1 row affected (0.00 sec)

3.  在slave查看

MariaDB [(none)]> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| testdb1            |

+--------------------+

4 rows in set (0.00 sec)

mysql 主從同步-讀寫分離