MySQL master/slave cluster installation and configuration

This document describes MySQL's master/slave cluster installation and configuration, and the installed version is the latest stable version of GA 5.6.19.

To support the limited ha, we use master/slave simple read-write separation cluster. Limited ha means that data is not lost when Master is unavailable, but is not writable in the case of master downtime and must be handled manually. If you want to support higher availability, you can use two master for hot switching.

The MySQL installation of master and slave is the same, except that the configuration of my.cnf is different and the binary log file replication needs to be configured.

There is no special description, named with # in the root user operation, with $ for the operation of the MySQL Linux user.

Installation Preparation

1. Before installing MySQL, it is necessary to verify that the following system software is installed on Linux.

Software name	Software description
gcc-4.4.7	Programming language compilers
gcc-c++-4.4.7	C + + language compiler
Cmake-2.6.4-5	A cross-platform open source building system
ncurses-devel-5.7-3.20090208	Controlling the Print console screen

2. Create a MySQL Linux user

# Groupadd MySQL

# useradd-g MySQL MySQL

# passwd MySQL

3. Prepare the installation directory

Create the MySQL installation directory and assign permissions to the MySQL User:

# mkdir/usr/local/mysql-5.6.19

# chown mysql:mysql/usr/local/mysql-5.6.19

# Chmod-r 770/usr/local/mysql-5.6.19

4. Create a MySQL data store directory:

# Mkdir/data

# Mkdir/data/mysql

# chown Mysql:mysql/data/mysql

5. Authorize MySQL to extract the source directory/usr/local/src directory executable permissions to all users:

# Chmod-r 757/USR/LOCAL/SRC

Install MySQL

1. Unzip the installation package:

$ cd/usr/local/src

$ TAR-XZVF mysql-5.6.19.tar.gz

2. Configure MySQL compilation parameters

$ cd/usr/local/src/mysql-5.6.19

$ cmake.-dcmake_install_prefix=/usr/local/mysql-5.6.19\

-dmysql_datadir= $MYSQL _data_path\

-dsysconfdir=/usr/local/mysql-5.6.19/conf\

-ddefault_charset=utf8\

-ddefault_collation=utf8_general_ci\

-dwith_readline=1\

-dwith_innobase_storage_engine=1\

-dwith_archive_storage_engine =1 \

-dwith_blackhole_storage_engine =1 \

-dwith_perfschema_storage_engine=1

Note:

-dcmake_install_prefix: Configuration MySQL the installation directory.

-dmysql_datadir: Configuration MySQL the Data directory.

-dsysconfdir: Configuration MySQL configuration file directory.

-ddefault_charset: The default character set.

-ddefault_collation : Sets the collation for the default language.

-dwith_readline : Support Bulk Import MySQL data.

-dwith_innobase_storage_engine : Using Innobase storage engine.

-dwith_archive_storage_engine : Often applied to logging and aggregation analysis, does not support indexing.

-dwith_blackhole_storage_engine : Black hole storage engine.

-dwith_perfschema_storage_engine: Performance mode engine.

3. Execute make

$ make

$ make Install

4. Modify the MYQL user's environment variables, add mysql_home, and add bin to path:

$ VI ~/.bash_profile

Add the Blue Font section to the file:

# User specific environment and startup programs # MySQL Home Catalogue Export mysql_home=/usr/local/mysql-5.6.19 path= $PATH: $HOME/bin : $MYSQL _home/bin Export PATH

$ source ~/.bash_profile

Initialize MySQL

1. Install the service script

# Cp/usr/local/mysql-5.6.19/support-files/mysql.server/etc/init.d/mysqld

# chown Mysql:mysql/etc/init.d/mysqld

# chmod 700/etc/init.d/mysqld

2. Create MySQL rights database

$ $MYSQL _home/scripts/mysql_install_db--basedir= $MYSQL _home--datadir=/data/mysql

3. Create a PID file directory

$ mkdir $MYSQL _home/var

4. Configure MY.CNF for Master

MY.CNF format is not correct, it is easy to start error, preferably on the original file based on the VI tool on Linux modified.

If the file is corrupted, you can copy it through the default template:

$ CP $MYSQL _HOME/SUPPORT-FILES/MY-DEFAULT.CNF$MYSQL_HOME/MY.CNF

Edit MY.CNF

$ VI $MYSQL _HOME/MY.CNF

Add the Blue Font section to the file:

# for advice The change settings # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html [Mysqld] # Remove Leading # and set to the amount of RAM for the most important data # Cache in MySQL. Start at 70% of all RAM for dedicated server, else 10%. Innodb_buffer_pool_size = 256M Innodb_flush_log_at_trx_commit=1 # Remove Leading # to turn on a very important data integrity option:logging # Changes to the binary log between backups. Log_bin=master-bin Log_bin_index=master-bin.index # These is commonly set, remove the # and set as required. # Basedir = ... DataDir =/data/mysql Port = 3306 # first master server ID server_id = 1 Socket =/tmp/mysql.sock Pid-file = /usr/local/mysql-5.6.19/var/master.pid # Remove Leading # To set options mainly useful for reporting servers. # The server defaults is faster for transactions and fast selects. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M Sql_mode=no_engine_substitution,strict_trans_tables

5. Configure MY.CNF for slave

Same as the master configuration file, but note that the log file name, DataDir, and server_id are different.

Edit MY.CNF

$ VI $MYSQL _HOME/MY.CNF

Add the Blue Font section to the file:

# for advice The change settings # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html [Mysqld] # Remove Leading # and set to the amount of RAM for the most important data # Cache in MySQL. Start at 70% of all RAM for dedicated server, else 10%. Innodb_buffer_pool_size = 256M Innodb_flush_log_at_trx_commit=1 # Replication # Relay-log=slave-relay-bin # Relay-log-index=slave-relay-bin.index # These is commonly set, remove the # and set as required. # Basedir = ... DataDir =/data/mysql Port = 3306 # first slave server ID of Master 1 server_id = 101 Socket =/tmp/mysql.sock Pid-file = /usr/local/mysql-5.6.19/var/slave.pid # Remove Leading # To set options mainly useful for reporting servers. # The server defaults is faster for transactions and fast selects. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M Sql_mode=no_engine_substitution,strict_trans_tables

6. Start MySQL

Start and stop master and slave are exactly the same.

$ service mysqldstart

Use the following command to see if the startup was successful:

$ service mysqldstatus

7. Stop MySQL

$ Service Mysqld Stop

Managing MySQL Security

The default MySQL root user password is empty, in order to improve security, you should set the root user a secure password.

On the server, open the MySQL client with a MySQL user:

$ mysql–u Root

To set a secure password:

mysql> SET PASSWORD [email Protected]=password (' secret ');

To configure replication between master and slave

1. Create a MySQL user to perform the replication

Create a replication user on Master, where secret is the user's password:

Mysql> CREATEUSER Repl_user identified by ' secret ';

and assigning Permissions to replication:

Mysql>grantreplication SLAVE on * * to Repl_user;

2. Lock master and get binary log location values

Get read Lock:

mysql> FLUSH TABLES withread LOCK;

Displays the current binary file name and location values:

mysql> SHOW MASTER STATUS;

3. Get master data snapshot via Mysqldump tool

Execute on Master in another session:

$ mysqldump-u root-p--all-databases--master-data > Data_dump.sql

After execution, it is saved in the current directory.

4. Release the read lock on Master

In the session that gets the read lock, execute the release lock command:

mysql> UNLOCK TABLES;

5. Import dump data into slave

Through the SCP copy Data_dump.sql to the slave machine.

$ SCP [email protected]<ip of Slave>:/home/mysql

On slave, use the following command to import to slave MySQL.

$ mysql–u Root-p

mysql>source Data_dump.sql;

6. Configuring replication between Master and slave

mysql> Change MASTER to master_host= ' <ip or hostame of master> ',

Master_user= ' Repl_user ',

Master_password= ' secret ',

Master_port = 3306,

Master_log_file= ' master-bin.000003 ',

master_log_pos=881;

The above master_log_file and Master_log_pos need to configure the information that is queried in 6.2. <ipor Hostame of master> is the IP or host name of master.

Start slave:

Mysql> START SLAVE;

7. Verifying the status of Master and slave

Execute on Master:

mysql> SHOW MASTER STATUS;

Execute on Slave:

mysql> SHOW SLAVE STATUS;

The above information shows that there is no error, the slave_io_state information is ' Waitigfor Master to send event ', which indicates the replication connection configuration OK.

At this point, the two MySQL master/slave mode cluster has been successfully deployed and can perform data update operations on master, and the discovery can be replicated to slave.

MySQL master/slave cluster installation and configuration