普通使用者Mysql 5.6.13 主從

標籤：使用   os   io   strong   檔案   資料   for   art   

Master:192.168.209.19

Slave:192.168.209.20

mysql版本:mysql5.6.13

 

1. 以root身份建立普通使用者,如mysql,並建立mysql安裝目錄:

   # useradd mysql

   # passwd mysql

   # mkdir /mysql

   # chown mysql:mysql /mysql

安裝依賴包：

yum -y install  gcc gcc-c++ autoconf automake zlib* fiex* libxml* ncurses-devel libmcrypt* libtool-ltdl-devel* make cmake

2. 下載MySQL5.6.13完整開發版, 並將其拷貝到/mysql目錄下.

   檔案: mysql-5.6.13.tar.gz

 

3. 以mysql使用者解包MySQL,並將MySQL解包後的目錄更改為/mysql/:

   # su - mysql

   $ cd

   $ mv mysql-5.6.13.tar.gz /mysql

   $ tar xvzf mysql-5.6.13.tar.gz

4. 配置MySQL:

   $ cd /mysql/5.6.13

$cmake -DCMAKE_INSTALL_PREFIX=/mysql/mysql-5.6

$make

$make install

$cd /mysql/mysql-5.6/scripts

$ ./mysql_install_db --user=mysql --basedir=/mysql/mysql-5.6 --datadir=/mysql/mysql-5.6/data

#cp /mysql/mysql-5.6/support-files/mysql.server /etc/init.d/mysql

#cp /mysql/mysql-5.6/support-files/my-default.cnf /etc/my.cnf

啟動mysql

$/mysql/mysql-5.6/support-files/mysql.server start

error解決：

[ERROR] Fatal error: Can‘t open and lock privilege tables: Table ‘mysql.user‘ doesn‘t exist

初始化資料庫就ok，即：./mysql_install_db --user=mysql --basedir=/mysql/mysql-5.6 --datadir=/mysql/mysql-5.6/data

5.配置主從：

5.1、主從伺服器分別操作：

  5.1.1、mysql版本一致

  5.1.2、初始化表，啟動mysql

  5.1.3、設定mysql root密碼， /mysql/mysql-5.6/bin/mysqladmin -u root password ‘123456’

5.2、修改master:

   #vi /etc/my.cnf

       [mysqld]

       log-bin=mysql-bin   #啟用二進位日誌

       server-id=20       #伺服器ID標示，主從不一致

 

5.3、修改slave:

   #vi /etc/my.cnf

       [mysqld]

       log-bin=mysql-bin   #啟用二進位日誌

       server-id=21      #伺服器ID標示，主從不一致

5.2 登入mysql master配置

#/mysql/mysql-5.6/bin/mysql -uroot -p123456 

   mysql>GRANT REPLICATION SLAVE ON *.* to ‘test‘@‘%‘ identified by ‘123456‘; 

#一般不用root帳號，“%”表示所有用戶端都可能連，只要帳號，密碼正確，此處可用具體用戶端IP代替，如192.168.209.20，加強安全。

 

6、登入主伺服器的mysql，查詢master的狀態

   mysql>show master status;

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

   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

   | mysql-bin.000007 |      320 |              |                  |

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

   1 row in set (0.00 sec)

   註：擷取file和position。

 

7、配置從伺服器mysql Slave：

   mysql>change master to master_host=‘192.168.209.19‘,master_user=‘test‘,master_password=‘123456‘,master_log_file=‘mysql-bin.000007‘,master_log_pos=320;   #320為主伺服器的position，無單引號。

   Mysql>start slave;    //啟動從伺服器複製功能

8、檢查slave狀態：

mysql> show slave status\G

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.209.19

                  Master_User: mysync

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000007

          Read_Master_Log_Pos: 320

               Relay_Log_File: cdn20-relay-bin.000002

                Relay_Log_Pos: 283

        Relay_Master_Log_File: mysql-bin.000007

             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: 320

              Relay_Log_Space: 456

              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: 20

                  Master_UUID: 6a36aa69-1c81-11e4-8213-b8ca3af2484f

             Master_Info_File: /mysql/mysql-5.6/data/master.info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

           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

1 row in set (0.00 sec)

 

Slave_IO及Slave_SQL進程YES狀態，否則都是錯誤的狀態(如：其中一個NO均屬錯誤)。

至此，主從伺服器配置完成。

 

9、主從伺服器測試：

主伺服器Mysql，建立資料庫，並在這個庫中建表插入一條資料：

mysql> create database test1;

Query OK, 1 row affected (0.00 sec)

 

mysql> use test1

Database changed

mysql> create table test1(id int(4),name char(8));

Query OK, 0 rows affected (0.01 sec)

 

mysql> insert into test values(001,‘test1‘);

ERROR 1146 (42S02): Table ‘test1.test‘ doesn‘t exist

mysql> insert into test1 values(001,‘test1‘);

Query OK, 1 row affected (0.00 sec)

 

mysql> show databases;

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

| Database           |

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

| information_schema |

| mysql              |

| performance_schema |

| test               |

| test1              |

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

5 rows in set (0.00 sec)

 

從伺服器Mysql查詢：

 

mysql> show databases;

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

| Database           |

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

| information_schema |

| mysql              |

| performance_schema |

| test               |

| test1              |

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

5 rows in set (0.01 sec)

至此，mysql主從配置完成，可以正常使用；後續會加入叢集模式。