Shulou(Shulou.com)06/01 Report--

"basic environmental preparation:"

First, install two MySQL5.7.16 databases by installing the following steps:

1. Preparation of the system environment:

①: system yum source configuration:

[linux]

Name=linux hae

Baseurl= file:///media/

Gpgcheck=1

Gpgkey= file:///media/RPM-GPG-KEY-redhat-release

②: Mount the Linux7.1 system disk to install the necessary software

Yum-y install gcc* gcc-c++ ncurses ncurses-devel cmake bison libgcrypt perl

③: uninstall the MySQL software that comes with the system:

[root@localhost ~] # rpm-qa | grep maria*

[root@localhost ~] # rpm-qa | grep mysql

[root@localhost] # rpm-e-nodeps mariadb-server

[root@localhost] # rpm-e-nodeps mariadb

④: modifying system parameters

[root@localhost ~] # vim / etc/security/limits.conf

* soft nproc 65535

* hard nproc 65535

* soft nofile 65535

* hard nofile 65535

[root@localhost ~] # vi / etc/profile

Ulimit-u 65535

Ulimit-n 65535

[root@localhost ~] # vim / etc/pam.d/login

Session required pam_limits.so

[root@localhost ~] # source / etc/profile

⑤: create the necessary directory to install MySQL

[root@localhost ~] # useradd mysql

[root@localhost ~] # passwd mysql

[root@localhost] # mkdir-p / mysql/mysql

[root@localhost] # mkdir-p / mysql/data

[root@localhost] # chown-R mysql.mysql / mysql

[root@localhost] # chmod-R 755 / mysql

Start installing MySQL5.7.16

①: decompress MySQL software

[mysql@localhost mysql] $tar-zxvf boost_1_59_0.tar.gz

[mysql@localhost mysql] $tar-zxvf mysql-5.7.16.tar.gz

②: compiling MySQL5.7.16

[mysql@localhost mysql-5.7.16] $cmake. -DCMAKE_INSTALL_PREFIX=/mysql/mysql\

-DMYSQL_DATADIR=/mysql/data\

-DDOWNLOAD_BOOST=1\

-DWITH_BOOST=/mysql/boost_1_59_0

[mysql@localhost mysql-5.7.16] $make-j 4

[mysql@localhost mysql-5.7.16] $make install

③: initialize the database

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

(after initializing the database, the default password for the root user is generated, which should be remembered.)

④: edit the my.cnf configuration file, start the database

[mysql@localhost 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 of the slave library can be set to 2 and not the same as the master 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

Start the MySQL database:

/ mysql/mysql/bin/mysqld_safe-defaults-file=/mysql/data/my.cnf &

⑤: start the database to change the default password

[mysql@localhost ~] $mysql-u root-p (use the password just now)

Note: if prompted that the password expires and does not allow login, you can add it in the my.cnf configuration file:

[mysqld]

Skip-grant-tables

Then restart MySQL and log in to the database without entering a 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 users (in the main library)

Mysql > grant replication slave,replication client on *. * to repl@'192.168.2.200' identified by 'feige'

#

Gtid replication operation

1. Operation of the main library

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 main MySQL library

3. Create schema and tables for testing 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 enabled gtid replication in the main library, and we have created test schema and tables, and we can see the current gtid number: 1-3 by looking at the status.

Second, operate from the library:

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

Gtid-mode = on

Enforce-gtid-consistency = 1

Binlog_format = row

Skip_slave_start=1

2. Restart the main MySQL 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. Enable slave master and slave synchronization:

Mysql > start slave

5. Start verifying the master-slave synchronization status:

Note: when master and slave are enabled, the test library and tables just created by the master library will be copied, because when the master library opens gtid, every operation of the master library will have a gtid number, and when synchronization is enabled from the slave library, all recorded operations will be synchronized to the slave database.

Note: there are many restrictions on gtid replication:

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.