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

Source: Internet
Author: User
Tags create database

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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.