MySQL multi-instance, master-slave synchronization

Due to background reasons, the master-slave synchronization is to be based on the MySQL 5.1 version, master-slave synchronization is mainly a database read and write access to the original database heat is too large, need to be used from the library to read the sub-pressure.

MySQL Master-Slave synchronization Introduction

MySQL supports single-bidirectional, chained-cascade, asynchronous replication.    During the replication process, one server acts as the primary server (master), and one or more other servers act as slave servers (Slave). If chained cascade replication is set, the slave (slave) server itself, in addition to acting as a slave server, also acts as the primary server from the server under it.    Chained cascade replication is similar to the a->b->c->d replication form. When master-slave replication is configured, all updates to the database content must be made on the primary server to avoid conflicts between updates to the data content on the primary server and updates to the database content from the server. In a production environment, it is common to ignore the authorization table synchronization and then grant the Select Read permission to the user from the server, or add the read-only parameter to the MY.CNF configuration file to ensure that the library is read-only and, of course, it works better. the principle of MySQL master-slave replicationMySQL master-slave replication is an asynchronous copy process (but it also looks real-time), and the database data is copied from one MySQL database (we call master) to another MySQL database (which we refer to as slave). The process of implementing the entire master-slave replication between master and slave has three threads participating in the completion.   Of these, two threads (SQL thread and IO thread) are on the slave side, and the other thread (IO thread) is on the master side. To achieve MySQL master-slave replication, you must first turn on the master side of the Binlog (mysql-bin.xxxxx) function, or you can not achieve master-slave replication. Because the entire replication process is essentially slave getting the Binlog log from the master, and then performing the various operations recorded in the Binlog log in the same order on slave itself. Open MySQL Binlog can add the "log-bin" parameter entry through the MYSQLD module in MySQL configuration file my.cnf ([mysqld] The parameter section after the identification). MySQL master-slave copy process descriptionThe following is a brief description of the replication process for MySQL replication: Start slave on the 1.Slave server, and the master-slave copy switch is turned on. 2. At this point, the IO thread of the slave server connects to the master server by requesting a copy user authorized on master and requests a specified location from the specified Binlog log file (log file and location are the change master when configuring the master-Slave service The Binlog log content after the specified time. After the 3.Master server receives a request from an IO thread from the slave server, the master server is responsible for copying the IO thread to read Binlog log information after the specified Binlog log file specified location according to the information requested by the IO thread of the slave server. The IO thread is then returned to the slave side. The information returned is in addition to the log content, and this time the return log content on the master server side of the new Binlog file name and in Binlog in the specified location. 4. When the IO thread of the slave server acquires the log contents and log files and location points from the IO thread on the master server, the Binlog log contents are written sequentially to the relay of the slave side itself Log (that is, the trunk log) file (mysql-relay-bin.xxxxx), and the new Binlog file name and location are recorded in the Master-info file. So that the next time you read the new Binlog log on the master side, you can tell the master server to start requesting new Binlog log content from the same location as the file from the Binlog log. 5. The SQL thread of the slave server detects the newly added log content in the local relay log in real time, and then resolves the contents of the log file into the contents of the SQL statements executed on the master side in a timely manner. These SQL statements are executed in the order of the statements on their own slave server. 6. The above procedure ensures that the same SQL statements are executed on the master and slave sides. When the replication status is normal, the data on the master side and the slave end is exactly the same. Schematic diagram of master-slave replication specific implementation of master-slave replicationThe following describes the installation of the test environment, about the online environment is not much to say, the steps are as follows: single-instance installation steps

1. Create a MySQL account

#groupadd MySQL #useradd-s/sbin/nologin-g mysql-m MySQL

#tail-L/ETC/PASSWD

Build MySQL Software Catalog

#mkdir-P/home/tools

#cd/home/tools/

2. Compiling and installing the MySQL software (http://down1.chinaunix.net/distfiles/mysql-5.1.62.tar.gz)

#tar zxf mysql-5.1.62.tar.gz #cd mysql-5.1.62

Configuration

./configure \

--prefix=/usr/local/mysql \

--with-unix-socket-path=/usr/local/mysql/tmp/mysql.sock \

--localstatedir=/usr/local/mysql/data \

--enable-assembler \

--enable-thread-safe-client \

--with-mysqld-user=mysql \

--with-big-tables \

--without-debug \

--with-pthread \

--enable-assembler \

--with-extra-charsets=complex \

--WITH-SSL \

--with-embedded-server \

--enable-local-infile \

--with-plugins=partition,innobase \

--with-plugin-plugin \

--with-mysqld-ldflags=-all-static \

--with-client-ldflags=-all-static

3. Static compilation of the execution file that generates MYSQLD

#make

4. Install MySQL

#make Install

5. Get the MySQL configuration file

#ls-L SUPPORT-FILES/*.CNF #cp support-files/my-small.cnf/etc/my.cnf

6. Create a database file

#mkdir-P/usr/local/mysql/data #chown-R mysql.mysql/usr/local/mysql

#/usr/local/mysql/bin/mysql_install_db--user=mysql

#

7. Start the MySQL database

#cp support-files/mysql.server/usr/local/mysql/bin #netstat-lnt|grep 3306

#/user/local/bin/mysql_safe--user=mysql &

8. Configure the global use path for MySQL commands

#echo ' Export path= $PATH:/usr/local/mysql/bin ' >>/etc/profile #source/etc/profile

9. Configure the/ETC/INIT.D/MYSQLD start mode startup database

#cp support-files/mysql.server/etc/init.d/mysqld #chmod 700/etc/init.d/mysqld

#/etc/init.d/mysqld restart

Multi-Instance Installation

1. use a different port as the level two directory

Mkdir-p/data/{3306,3307}/data

2 Creating a MySQL multi-instance configuration file

Ls-l support-files/*.cnf /BIN/CP support-files/my-small.cnf/etc/my.cnf

3 Add the following through the VI command:

Vi/data/3306/my.cnf Vi/data/3307/my.cnf

MY.CNF Configuration

[Client] Port = 3306 Socket =/data/3306/mysql.sock [MySQL] No-auto-rehash [Mysqld] user = MySQL Port = 3306 Socket =/data/3306/mysql.sock Basedir =/usr/local/mysql DataDir =/data/3306/data Open_files_limit = 1024 Back_log = 600 Max_connections = 800 Max_connect_errors = 3000 Table_cache = 614 external-locking = FALSE Max_allowed_packet =8m Sort_buffer_size = 1M Join_buffer_size = 1M thread_cache_size = 100 Thread_concurrency = 2 Query_cache_size = 2M Query_cache_limit = 1M Query_cache_min_res_unit = 2k Default_table_type = InnoDB Thread_stack = 192K Transaction_isolation = read-committed Tmp_table_size = 2M Max_heap_table_size = 2M Long_query_time = 1 Log_long_format Log-error =/data/3306/error.log Log-slow-queries =/data/3306/slow.log Pid-file =/data/3306/mysql.pid Log-bin =/data/3306/mysql-bin Relay-log =/data/3306/relay-bin Relay-log-info-file =/data/3306/relay-log.info Binlog_cache_size = 1M Max_binlog_cache_size = 1M Max_binlog_size = 2M Expire_logs_days = 7 Key_buffer_size = 16M Read_buffer_size = 1M Read_rnd_buffer_size = 1M Bulk_insert_buffer_size = 1M Myisam_sort_buffer_size = 1M Myisam_max_sort_file_size = 10G Myisam_max_extra_sort_file_size = 10G Myisam_repair_threads = 1 Myisam_recover Lower_case_table_names = 1 Skip-name-resolve Slave-skip-errors = 1032,1062 Replicate-ignore-db=mysql Server-id = 1 Innodb_additional_mem_pool_size = 4M Innodb_buffer_pool_size = 32M Innodb_data_file_path = Ibdata1:128m:autoextend Innodb_file_io_threads = 4 Innodb_thread_concurrency = 8 Innodb_flush_log_at_trx_commit = 2 Innodb_log_buffer_size = 2M Innodb_log_file_size = 4M Innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 Innodb_lock_wait_timeout = 120 innodb_file_per_table = 0 [Mysqldump] Quick Max_allowed_packet = 2M [Mysqld_safe] Log-error=/data/3306/mysql_barry3306.err Pid-file=/data/3306/mysqld.pid

4 Creating a MySQL multi-instance startup file startup file MySQL 3306

#!/bin/sh#/data/3306/mysql Script #init port=3306 Mysql_user= "Root" Mysql_pwd= "" Cmdpath= "/usr/local/mysql/bin" #startup function Function_start_mysql () { printf "Starting mysql...\n" /bin/sh ${cmdpath}/mysqld_safe--defaults-file=/data/${port}/my.cnf 2>&1 >/dev/null & } #stop function Function_stop_mysql () { printf "stoping mysql...\n" ${cmdpath}/mysqladmin-u ${mysql_user}-p${mysql_pwd}-s/data/${port}/mysql.sock shutdown } #restart function Function_restart_mysql () { printf "Restarting mysql...\n" Function_stop_mysql Sleep 2 Function_start_mysql } Case $ in Start Function_start_mysql ;; Stop Function_stop_mysql ;; Restart Function_restart_mysql ;; *) printf "Usage:/data/${port}/mysql {start|stop|restart}\n" Esac

5 directory Structure

Tree/data/data --3306 |--my.cnf |--mysql |--data--3307 |--my.cnf |--mysql |--data #授权chown-r mysql.mysql/datafind/data-name mysql-exec chmod 700 {} \

6 Configuring the MySQL command global use path to configure global path meaning if you do not configure the global path of the MySQL command, you will not be able to directly knock commands such as MySQL, which can only be/usr/local/msyql/bin/mysql.

Echo ' Export path= $PATH:/usr/local/mysql/bin ' >>/etc/profile Source/etc/profile

7 Creating a MySQL multi-instance data file

mysql_install_db--datadir=/data/3306/data--user=mysql mysql_install_db--datadir=/data/3307/data--user=mysql

8 starting the MySQL multi-instance database

/data/3306/mysql start /data/3307/mysql start #检查MySQL数据是否启动 Netstat-lnt|grep 330[6,7]

9 Configuring MySQL Multi-instance database boot automatically

echo "/data/3306/mysql start" >>/etc/rc.localecho "/data/3307/mysql start" >>/etc/rc.local

10 Login MySQL Test

Mysql-s/data/3306/mysql.sockmysql-s/data/3307/mysql.sock

MySQL Security Configuration for root add password

Mysqladmin-u root-s/data/3306/mysql.sock password ' barry123 ' #<-change the default password. Mysql-s/data/3306/mysql.sock #<-cannot log in directly Mysql-uroot-p-s/data/3306/mysql.sock #<-New Login method

View and clean up redundant users

Select User,host Form Mysql.user

Master-Slave Replication configuration

The master library, known as Master, is called Slave from the library. 1. Performing actions on the main library(1) Setting the Server-id value and turning on the Binlog setting based on the synchronization principle of the previous MySQL, we know that the key factor of replication is the Binlog log. Execute VI/DATA/3306/MY.CNF Edit my.cnf configuration file, modify the contents as follows two parameters:

[Mysqld] Server-id =1 Log-bin=/data/3306/mysql-bin

Check the results after configuration

Grep-e "Server-id|log-bin"/data/3306/my.cnf Log-bin=/data/3306/mysql-bin Server-id=1

(2) Set up account rep for synchronization

Mysql-uroot-p '-s/data/3306/mysql.sock Grant Replication Slave on * * to ' rep ' @ ' 10.0.0.% ' identified by ' password ';

(3) Lock table read-only (do not close the current window) production environment, the operation of master-slave replication, the need to apply for downtime, lock table will affect the business.

Flush tables with read lock; Interactive_timeout=60 Wait_timeout=60

(4) View the main library Status View the main library status, that is, the current log file name and binary offset the show Master Status command displays the information to be recorded, and the subsequent copy from the library is synchronized from this location. (5) Export Database data Sheet Open a new window, export database data, if the amount of data is large, you can stop the library directly packaged data file migration.

mkdir/server/backup/-P Mysqldump-uroot-p ' password '-s/data/3306/mysql.sock-a-b |gzip >/server/backup/mysql_bak.${date +%F}.sql.gz Ls-l/server/backup/mysql_bak.${date +%f}.sql.gz

In order to ensure that the database does not have data inserted during the guide, you can check the main library status information again

Mysql-u root-p ' password '-s/data/3306/mysql.sock-e "Show Master Status"

After the library, unlock the main library and resume writable:

Unlock tables;

(6) Migrating the MySQL data from the main library to the library This is not commonly used commands such as Scp,rsync, the relevant command before the course has been explained in detail, here is not much description. This article is about a single-database multi-instance master-slave configuration, so the data is on one machine, looking at the data 2 performing operations from the library(1) Setting the Server-id value and closing the Binlog setting the Server-id of the database is generally unique within the LAN, where Server-id is different from the main library and other libraries, and resolves the binlog parameter configuration from the library to execute vi/data/3307/ MY.CNF configuration file, modified according to the following two parameters:

[Mysqld] server-id=2 #log-bin=/data/3307/mysql-bin

(2) restoring data back to the original repository export

Gzip-d mysql_bak.2014-04-17.sql.gz Mysql-uroot-p ' password '-s/data/3307/mysql.sock < Mysql_bak.2014-04-17.sql

(3) Login to configure synchronization parameters from the library

Mysql-uroot-p ' Password '-s/data/3307/mysql.sock Change MASTER to Master_host= ' 10.0.0.x ', <== here is the IP of the main library master_port=3306, <== here is the port of the main library, which can differ from the main library port. Master_user= ' rep ', <== here is the user rep created on the main library for replication Master_password= ' PASSWORD ', Master_log_file= ' mysql-bin.0000008 ', <== here is the name of the binary file found when show master status master_pos=342;<== This is the show master status when you see the binary log offset, note that there can be no more spaces.

Do not log in to the database and quickly execute the Change master statement on the command line (suitable for batch building of slave libraries in scripts) This method is used to manipulate

Cat |mysql-uroot-p ' password '-s/data/3307/mysql.sock<< eofchange MASTER tomaster_host= ' 10.0.0.x ', master_port=3306, Master_user= ' rep ', Master_password= ' PASSWORD ', Master_log_file= ' mysql-bin.0000008 ', master_log_pos=342; Eof

You can also log in to the database to execute the following statement:

Change MASTER tomaster_host= ' 192.168.1.234 ', master_port=3306, Master_user= ' rep ', Master_password= ' PASSWORD ', Master_log_file= ' mysql-bin.000010 ', master_log_pos=261;

(4) Start the sync switch from the library, start the sync switch from the library, and view the sync status

Mysql-uroot-p ' password '-s/data/3307/mysql.sock-e "start slave;" Mysql-uroot-p ' password '-s/data/3307/mysql.sock-e "show slave status\g;"

To determine if replication is successful, see if the following IO and SQL two threads are displayed as Yes status Slave_io_running:yes is responsible for reading the Binlog log from the library to the main library and writing it from the library's trunk log Slave_sql_runnint:yes Responsible for reading and relaying the log in binlog, converting the SQL statement after applying to the database rollup Ok, this whole process is basically done.

MySQL multi-instance, master-slave synchronization