MySQL5.7.16 Gtid Replication

"Basic Environment Preparation:"

First install two MySQL5.7.16 database, install the following steps:

First, the system environment preparation:

①: System Yum Source configuration:

[Linux]

Name=linux Hae

baseurl=file:///media/

Gpgcheck=1

Gpgkey=file:///media/rpm-gpg-key-redhat-release

②: Mount the necessary software for installing the Linux7.1 system disk

Yum-y Install gcc* gcc-c++ ncurses ncurses-devel cmake Bison libgcrypt Perl

③: Uninstall the system comes with the MySQL software:

[Email protected] ~]# Rpm-qa | grep maria*

[Email protected] ~]# Rpm-qa | grep MySQL

[Email protected] ~]# rpm-e--nodeps mariadb-server

[Email protected] ~]# rpm-e--nodeps mariadb

④: Modifying system parameters

[Email protected] ~]# vim/etc/security/limits.conf

* Soft Nproc 65535

* Hard Nproc 65535

* Soft Nofile 65535

* Hard Nofile 65535

[Email protected] ~]# Vi/etc/profile

Ulimit-u 65535

Ulimit-n 65535

[Email protected] ~]# Vim/etc/pam.d/login

Session Required Pam_limits.so

[Email protected] ~]# Source/etc/profile

⑤: Create the necessary directory to install MySQL

[[email protected] ~]# useradd MySQL

[[email protected] ~]# passwd MySQL

[Email protected] ~]# mkdir-p/mysql/mysql

[Email protected] ~]# mkdir-p/mysql/data

[Email protected] ~]# chown-r mysql.mysql/mysql

[Email protected] ~]# chmod-r 755/mysql

Ii. Start installation of MySQL5.7.16

①: extracting MySQL Software

[Email protected] mysql]$ TAR-ZXVF boost_1_59_0.tar.gz

[Email protected] mysql]$ TAR-ZXVF mysql-5.7.16.tar.gz

②: Compiling MySQL5.7.16

[Email protected] mysql-5.7.16]$ CMake. -dcmake_install_prefix=/mysql/mysql \

-dmysql_datadir=/mysql/data \

-ddownload_boost=1 \

-dwith_boost=/mysql/boost_1_59_0

[Email protected] mysql-5.7.16]$ Make-j 4

[[email protected] mysql-5.7.16]$ make install

③: Initializing the data base

/mysql/mysql/bin/mysqld--initialize--user=mysql--basedir=/mysql/mysql/--datadir=/mysql/data/

(After initializing the database, the root user's default password will be generated, remember)

④: Edit the my.cnf configuration file to start the database

[Email protected] data]$ vim my.cnf

[Client]

Port = 3306

Socket =/mysql/mysql/mysql.sock

Default-character-set = Utf8mb4

[Mysqld]

Port = 3306

Socket =/mysql/mysql/mysql.sock

Basedir =/mysql/mysql

DataDir =/mysql/data

Pid-file =/mysql/data/mysql.pid

user = MySQL

Bind-address = 0.0.0.0

Server-id = 1 ------The number from the library can be set 2 cannot be consistent with the main library;

Init-connect = ' SET NAMES utf8mb4 '

Character-set-server = Utf8mb4

Back_log = 300

max_connections = 1000

Max_connect_errors = 6000

Open_files_limit = 65535

Table_open_cache = 128

Max_allowed_packet = 4M

Binlog_cache_size = 1M

Max_heap_table_size = 8M

Tmp_table_size = 16M

Read_buffer_size = 2M

Read_rnd_buffer_size = 8M

Sort_buffer_size = 8M

Join_buffer_size = 8M

Key_buffer_size = 4M

Thread_cache_size = 8

Query_cache_type = 1

Query_cache_size = 8M

Query_cache_limit = 2M

Ft_min_word_len = 4

Log_bin = Mysql-bin

Expire_logs_days = 30

Log_error =/mysql/data/mysql-error.log

Slow_query_log = 1

Long_query_time = 1

Slow_query_log_file =/mysql/data/mysql-slow.log

Performance_schema = 0

Explicit_defaults_for_timestamp

Skip-external-locking

Default_storage_engine = InnoDB

innodb_file_per_table = 1

Innodb_open_files = 500

Innodb_buffer_pool_size = 64M

Innodb_write_io_threads = 4

Innodb_read_io_threads = 4

innodb_thread_concurrency = 0

Innodb_purge_threads = 1

Innodb_flush_log_at_trx_commit = 2

Innodb_log_buffer_size = 2M

Innodb_log_file_size = 32M

Innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

Innodb_lock_wait_timeout = 120

Bulk_insert_buffer_size = 8M

Interactive_timeout = 28800

Wait_timeout = 28800

[Mysqldump]

Quick

Max_allowed_packet = 16M

[Myisamchk]

Key_buffer_size = 8M

Sort_buffer_size = 8M

Read_buffer = 4M

Write_buffer = 4M

To start the MySQL database:

/mysql/mysql/bin/mysqld_safe--DEFAULTS-FILE=/MYSQL/DATA/MY.CNF &

⑤: Start database to modify default password

[[email protected] ~]$ mysql-u root-p (using the password just now)

Note: If you are prompted for password expiration and do not allow login, you can add it in the MY.CNF profile:

[Mysqld]

Skip-grant-tables

Then restart MySQL, you can log into the database without entering the password!

Finally, change the default password:

mysql> Update Mysql.user Set Authentication_string=password (' 123456 ') where user= ' root ';

mysql> Update mysql.user set password_expired= ' N ';

mysql> flush Privileges;

mysql> alter user ' root ' @ ' localhost ' identified by ' 123456 ';

mysql> flush Privileges;

⑥: Create synchronization with the user (in the main library)

Mysql> Grant replication Slave,replication Client on * * to [e-mail protected] ' 192.168.2.200 ' identified by ' Feige ';

###########################################################################

Gtid copy Operation

First, the main library operation

1. Edit the my.cnf configuration file of the main library and add the following:

Gtid-mode = On

Enforce-gtid-consistency = 1

Binlog_format = row

Skip_slave_start=1

2, restart the MySQL main library;

3. Create a Test schema and table in the main library

mysql> CREATE database Zhang;

Mysql> use Zhang

Mysql> Show tables;

Mysql> CREATE TABLE emp (id int);

mysql> INSERT INTO EMP values (1);

Mysql> Show master status;

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

| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |

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

|      mysql-bin.000008 |              738 |                  | | Ac3842d2-ba9a-11e6-9e1b-000c298cd705:1-3 |

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

Note: We have gtid replication turned on in the main library, and we have created a test schema and table and can see the status of the current Gtid number: 1-3

Second, from the library operation:

1. Edit the my.cnf configuration file from the library and add the following:

Gtid-mode = On

Enforce-gtid-consistency = 1

Binlog_format = row

Skip_slave_start=1

2, restart the MySQL main library;

3, configure master-Slave synchronization:

mysql> change MASTER to master_host= ' 192.168.2.100 ', master_user= ' repl ', master_password= ' Feige ', master_port=3306 , master_auto_position = 1;

4. Turn on slave master sync from library:

mysql> start slave;

5. Start verifying master-Slave synchronization status:

Note: After the Master master, the main library has just created the test library and the table will be copied, because the main library when the main library to open Gtid every step of the operation will have a gtid number, waiting to open the synchronization from the library, will be all the records of operations will be synchronized to the library;

Note: Gtid replication has many limitations:

This article is from the "stupid Child's DBA path" blog, please be sure to keep this source http://fengfeng688.blog.51cto.com/4896812/1928541

MySQL5.7.16 Gtid Replication