Centos7.5 mysql 5をインストールします.7.24バイナリ・パッケージ方式の導入(マスター・スレーブ・レプリケーション)

8038 ワード

Centos7.5 インストール mysql5.7. データベース

一、環境準備:
OS:CentOS Linux release 7.5.1804(Core)mysqlバージョン:mysql-5.7.24-linux-glibc 2.12-x86_64.tar.gzメインライブラリ:172.16.8.247スレーブライブラリ:172.16.8.249ホスト名:172.16.8.247 qas-zabbix-node 01172.16.8.249 qas-zabbix-node02
二、mysql 5.7インストール
マスターライブラリ:172.16.8.247スレーブライブラリ:172.16.8.249データインストールリファレンス:一、Centos 7.5 mysql 5をインストールします.7.24バイナリパッケージ方式の配置
三、mysql主従レプリケーション構成
1、GTIDとバイナリログを有効にする:(メインライブラリ)

grep -n ^[a-Z] /app/mysql5.7/etc/my.cnf 
2:daemonize = on
3:user = mysql
4:port = 3306
5:basedir = /app/mysql5.7
6:datadir = /app/mysql5.7/data
7:socket = /tmp/mysql.sock
8:bind-address = 0.0.0.0
9:pid-file = /app/mysql5.7/tmp/mysqld.pid
10:character-set-server = utf8
11:collation-server = utf8_general_ci
12:max_connections = 2408
13:log-error = /app/mysql5.7/logs/mysqld.log
15:gtid_mode = ON
16:server_id = 247
17:log-bin = mysql-bin
18:enforce_gtid_consistency = ON

プロファイルを変更するにはmysqlデータベースsystemctl restart mysqldを再起動する必要があります
2、主従レプリケーションアカウント(主ライブラリ)の作成
同期ユーザを作成し、slaveはmasterと同期します.

mysql -uroot -p
grant replication slave on *.* to 'repl'@'172.16.8.%' identified by 'repl@2018';

3、GTIDとバイナリログを有効にする:(ライブラリから)

grep -n ^[a-Z] /app/mysql5.7/etc/my.cnf 
2:daemonize = on
3:user = mysql
4:port = 3306
5:basedir = /app/mysql5.7
6:datadir = /app/mysql5.7/data
7:socket = /tmp/mysql.sock
8:bind-address = 0.0.0.0
9:pid-file = /app/mysql5.7/tmp/mysqld.pid
10:character-set-server = utf8
11:collation-server = utf8_general_ci
12:max_connections = 2408
13:log-error = /app/mysql5.7/logs/mysqld.log
15:gtid_mode = ON
16:server_id = 249
17:log-bin = mysql-bin
18:log_slave_updates = ON 
19:enforce_gtid_consistency = ON
20:slave-parallel-type = LOGICAL_CLOCK
21:slave-parallel-workers = 8
22:master-info-repository = TABLE 
23:relay-log-info-repository = TABLE

4、複製接続の構成(ライブラリから)

CHANGE MASTER TO MASTER_HOST='172.16.8.247', MASTER_USER='repl', MASTER_PASSWORD='repl@2018', MASTER_AUTO_POSITION=1;

5、コピーステータスの表示:(ライブラリから)

mysql> show slave status\G;
Empty set (0.00 sec)

ERROR: 
No query specified

mysql>   CHANGE MASTER TO MASTER_HOST='172.16.8.247', MASTER_USER='repl', MASTER_PASSWORD='repl@2018', MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 172.16.8.247
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: 
          Read_Master_Log_Pos: 4
               Relay_Log_File: qas-zabbix-node02-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: 
             Slave_IO_Running: No
            Slave_SQL_Running: No
              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: 0
              Relay_Log_Space: 154
              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: NULL
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: 0
                  Master_UUID: 
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           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: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

6、レプリケーションスレッドの開始(ライブラリから)

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.8.247
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 194
               Relay_Log_File: qas-zabbix-node02-relay-bin.000003
                Relay_Log_Pos: 407
        Relay_Master_Log_File: mysql-bin.000002
             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: 194
              Relay_Log_Space: 1131
              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: 247
                  Master_UUID: a7fef663-f05e-11e8-b1f5-08002728f0e4
             Master_Info_File: mysql.slave_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: a7fef663-f05e-11e8-b1f5-08002728f0e4:1
            Executed_Gtid_Set: a7fef663-f05e-11e8-b1f5-08002728f0e4:1
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

四、テスト結果

（  ）
mysql> create database zabbix character set utf8 collate utf8_bin;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| qas                |
| sys                |
| test               |
| zabbix             |
+--------------------+

（  ）
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| qas                |
| sys                |
| test               |
| zabbix             |
+--------------------+

mysql> select user,host from mysql.user;
+---------------+------------+
| user          | host       |
+---------------+------------+
| root          | 172.16.0.% |
| repl          | 172.16.8.% |
| zabbix        | 172.16.8.% |
| mysql.session | localhost  |
| mysql.sys     | localhost  |
| root          | localhost  |
| zabbix        | localhost  |
+---------------+------------+

1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration a

sparkSQL MySQLデータソースからデータを読み込む【demoプログラム編ReadData FromMysql】