MySQL multi-instance, master-slave synchronization

Source: Internet
Author: User
Tags chmod mysql commands

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

Related Article

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.