Linux system Transport Koriyuki MySQL DB cluster deployment (master-slave replication)

First, Introduction

　　　　MySQL master-slave replication, the previous time the production environment deployed a master-slave replication of the architecture, at that time now found a lot of information, now recorded

Second, topology diagram

Third, environment and software version

Host Name	Ip	Operating system	Role	Software version
Mysqldb_master	192.168.0.1	CentOS Release 7.1	Master	Mysql 5.6.36
Mysqldb_slave	192.168.0.2	CentOS Release 7.1	Slave	Mysql 5.6.36

Iv. Installation of source code

　　　　Previously, Yum was installed directly, this time try to use the source installation, the installation package is as follows:

• Mysql-5.6.36.tar.gz
• Cmake-3.8.1.tar.gz

　　　　First install the prerequisite environment:

[Email Protected]_master Soft]YumGroupinstall'Development Tools'-y[[email Protected]_master Soft]Tar-XF cmake-3.8.1.Tar. Gz[[email Protected]_master Soft] CD CMake-3.8.1[[Email protected]_master cmake-3.8.1] ./bootstrap[[email protected]_master CMake-3.8.1] Gmake && GmkeInstall

install MySQL:

[Email Protected]_master Soft]Tar-XF mysql-5.6. $.Tar. Gz[[email Protected]_master Soft] CD MySQL-5.6. $[email protected]_master mysql-5.6. $] Cmake-dcmake_install_prefix=/data/opt/mysql-dmysql_datadir=/data/opt/mysql/data-ddefault_charset=utf8- ddefault_collation=utf8_general_ci-dextra_charsets=all-denabled_local_infile=1[email protected]_master mysql-5.6. $] Make&& Make Install

　　　　MySQL Configuration Related:

[[Email Protected]_master mysql-5.6. $]mkdir-p/data/opt/mysql/data/{3306, binlog,tmp} [Email protected]_master MySQL-5.6. $]Chown-R mysql.mysql/data/opt/mysql[[email protected]_master MySQL-5.6. $]CPsupport-files/my-default.cnf/etc/my.conf[[email protected]_master MySQL-5.6. $] vim/etc/my.conf===================================[Client]port=3306Socket=/data/opt/mysql/3306/mysql.sock#the MySQL server[mysqld]server-ID=209Innodb_flush_log_at_trx_commit=1Sync_binlog=1Port=3306User=Mysqlsocket=/data/opt/mysql/3306/Mysql.sockpid-file=/data/opt/mysql/3306/Mysql.pidbasedir=/data/opt/Mysqldatadir=/data/opt/mysql/Datatmpdir=/data/opt/mysql/Tmpopen_files_limit=10240Lower_case_table_names=1Explicit_defaults_for_timestampsql_mode=No_engine_substitution,strict_trans_tables#buffermax_allowed_packet=256mmax_heap_table_size=256mnet_buffer_length=8ksort_buffer_size=2mjoin_buffer_size=4mread_buffer_size=2mread_rnd_buffer_size=16m#loglog-bin =/data/opt/mysql/binlog/mysql-binbinlog_cache_size=32mmax_binlog_cache_size=512mmax_binlog_size=512mbinlog_format=Mixedlog_output=FileLog-error =/data/opt/mysql/binlog/mysql-Error.logslow_query_log=1Slow_query_log_file=/data/opt/mysql/binlog/Slow_query.loggeneral_log=0General_log_file=/data/opt/mysql/binlog/General_query.logexpire-logs-days = -#InnoDBinnodb_data_file_path=ibdata1:2048m:autoextendinnodb_log_file_size=256minnodb_log_files_in_group=3innodb_buffer_pool_size=1024m[mysql]no-auto-rehashprompt= (\[email protected]\h) [\d]>\_default-character-set =GBK===================================#添加mysql环境变量 [[email protected]_master MySQL-5.6. $]Echo 'export path= $PATH:/data/opt/mysql/bin'>>/etc/profile && source/etc/profile# initializing database [[email protected]_master MySQL-5.6. $] cd/data/opt/mysql[[email protected]_master MySQL]./scripts/mysql_install_db--defults-file=/ETC/MY.CNF--user=mysql[[email protected]_master MySQL]./bin/mysqld_safe--user=mysql &#添加service启动 [[email protected]_master MySQL]CP/soft/mysql-5.6. $/support-files/mysql.server/etc/init.d/mysqld[[email protected]_master MySQL]chmod+x/ect/init.d/mysqld

　　　　To optimize MySQL default configuration, configure access rights:

[Email protected]_master ~] Mysql-uroot-p# Empty default data ([email protected]) [(none)]>Select*From mysql.db \g ([email protected]) [(none)]>truncate table mysql.db; ([email protected]) [(none)]>flush privileges; ([email protected]) [(none)]>Select*From mysql.db \g# Configuring access rights ([email protected]) [(none)]>Use mysql; ([email protected]) [(none)]>desc user; ([email protected]) [(none)]> Grant all privileges on * * to Root@"%"Identified by"Root";([email protected]) [(none)]> Update user Set Password=password ('XXXXXX') Where user='Root';([email protected]) [(none)]>SelectHost,user,password from User where user='Root';([email protected]) [(none)]>flush privileges; ([email protected]) [(none)]> exit;

　　　　Above, MySQL source installation is complete, the same configuration can be done on the Mysqldb_slave server.

V. Configuration of Master-slave

　　　　About the master-slave configuration, previously in the configuration file has been completed, "Server-id" value master-slave, from the value is greater than the main value, the main configuration is as follows:

[Mysqld]server-ID=209#InnoDBinnodb_flush_log_at_trx_commit=1sync_ Binlog=1

　　　　Connect to the main library, configure the Sync account and authorize connections from the server:

([email protected]) [(none)] Grant replication Slave,reload,super on * * to'Repl'@'192.168.0.2'Identified by'Backup';([email protected]) [(none)] flush tables with read lock; ([email protected]) [(none)] Show master status;+------------------+----------+--------------+------------------+-------------------+| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |+------------------+----------+--------------+------------------+-------------------+| Mysql-bin.000007|321|                  |                   | |+------------------+----------+--------------+------------------+-------------------+1RowinchSet (0.00Sec

　　　　Connect to the library, configure the home server IP, and synchronize the account information:

([email protected]) [(None)] > Change master to master_host='192.168.0.1', master_user='repl  ', master_password='backup', master_log_file='  mysql-bin.000007', master_log_pos=321>> Show slave status \ G

　　　　Confirm the master-slave node synchronization is normal, the main confirmation of the following values:

Master_log_file:mysql-bin. 000007 relay_master_log_file:mysql-bin. 000007 Slave_IO_Running:YesSlave_SQL_Running:Yes

　　　　Connect to the main library, unlock, and create a new db:

([email protected]) [(none)] unlock tables; ([email protected]) [(none)] CREATE database TestDB;

　　　　Connect to the library to see if the new db is synchronized:

([email protected]) [(none)]  show databases; +--------------------+| Database           |+--------------------+| information_schema | | mysql              | | performance_schema | | TestDB             |+-------- ------------+

　　　　Above, MySQL cluster, master-Slave synchronization configuration is complete.

　　　　

Linux system Transport Koriyuki MySQL DB cluster deployment (master-slave replication)