MySQL Master/Slave cluster installation and configuration _ MySQL

This article describes how to install and configure the MasterSlave cluster of MySQL. The latest stable version is GA5619. To support limited HA, we use the MasterSlave simple read/write splitting cluster. Limited HA means that data will not be lost when the Master is unavailable, but in Mast this article describes the installation and configuration of the MySQL Master/Slave cluster. the installed version is the latest stable version GA 5.6.19.

To support limited HA, we use the Master/Slave simple read/write splitting cluster. Limited HA means that data will not be lost when the Master node is unavailable, but cannot be written when the Master node is down, and faults must be handled manually. To support higher availability, you can use two masters for hot switching.

The installation of MySQL on the Master and Slave is the same, but the configuration of my. cnf is different. you need to configure binary log file replication.

There is no special description. "#" in the name is the root user operation, and "$" is the mysql Linux user operation.

Installation preparation

1. before installing MySQL, make sure that the following system software has been installed in Linux.

Software name	Software description
Gcc-4.4.7	Programming language compiler
Gcc-c ++-4.4.7	C ++ compiler
Cmake-2.6.4-5	Cross-platform open-source construction system
Ncurses-devel-5.7-3.20090208	Control print console screen

2. create a mysql Linux user

# Groupadd mysql

# Useradd-g mysql

# Passwd mysql

3. prepare the installation directory

Create a MySQL installation directory and grant permissions to the mysql users:

# 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 storage directory:

# Mkdir/data

# Mkdir/data/mysql

# Chown mysql: mysql/data/mysql

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

# Chmod-R 757/usr/local/src

Install MySQL

1. decompress 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/The 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: configure the MySQL installation directory.

-DMYSQL_DATADIR: configure the MySQL data directory.

-DSYSCONFDIR: Directory of the configuration file for configuring MySQL.

-DDEFAULT_CHARSET: default character set.

-DDEFAULT_COLLATION: Set the sorting rules for the default language.

-DWITH_READLINE: mysql data can be imported in batches.

-DWITH_INNOBASE_STORAGE_ENGINE: the INNOBASE storage engine is used.

-DWITH_ARCHIVE_STORAGE_ENGINE: it is often used for log record and aggregate analysis. indexes are not supported.

-DWITH_BLACKHOLE_STORAGE_ENGINE: the black hole storage engine.

-DWITH_PERFSCHEMA_STORAGE_ENGINE: Performance mode engine.

3. execute make

$ Make

$ Make install

4. modify the environment variable of myql, add MYSQL_HOME, and add bin to PATH:

$ Vi ~ /. Bash_profile

Add the blue font to the file:

# User specific environment and startup programs # MySQL home directory 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 a mysql permission 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 The master

The format of my. cnf is incorrect and it is easy to make errors at startup. you are advised to modify it on linux using the vi tool based on the original file.

If the file is damaged, you can copy it in 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 to the file:

# For advice on how to change settings please see # 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 total RAM for dedicated servers, else 10%. Innodb_buffer_pool_size = 256 M 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 are 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 are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # Join_buffer_size = 128 M # Sort_buffer_size = 2 M # Read_rnd_buffer_size = 2 M SQL _mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES

5. configure my. cnf for slave

The configuration file is modified like the master configuration file, but note that the log file name, datadir, and server_id in the file are different.

Edit my. cnf

$ Vi $ MYSQL_HOME/my. cnf

Add the blue font to the file:

# For advice on how to change settings please see # 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 total RAM for dedicated servers, else 10%. Innodb_buffer_pool_size = 256 M Innodb_flush_log_at_trx_commit = 1 # Replication # Relay-log = slave-relay-bin # Relay-log-index = slave-relay-bin.index # These are 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 are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # Join_buffer_size = 128 M # Sort_buffer_size = 2 M # Read_rnd_buffer_size = 2 M SQL _mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES

6. start MySQL

The master and slave startup and stop operations are the same.

$ Service mysqldstart

Run the following command to check whether the startup is successful:

$ Service mysqldstatus

7. stop MySQL

$ Service mysqld stop

Manage MySQL security

By default, the root user password of MySQL is empty. to improve security, you should set a secure password for the root user.

Open the mysql client through the MySQL user on the server:

$ Mysql-u root

Set a secure password:

Mysql> set password FORroot @ localhost = PASSWORD ('secret ');

Configure replication between Master and Slave

1. create a MySQL user to execute replication

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

Mysql> CREATEUSER repl_user identified by 'secret ';

And grant the copy permission:

Mysql> grantreplication slave on *. * TO repl_user;

2. lock the Master and obtain the binary log location value

Get the read lock:

Mysql> flush tables withread lock;

Display the current binary file name and location value:

Mysql> show master status;

3. use mysqldump to get Master data snapshots

Run the following command on the Master node in another session:

$ Mysqldump-u root-p -- all-databases -- master-data> data_dump. SQL

After the command is executed, it is saved in the current directory.

4. release the read lock on the Master.

In the session for obtaining the read lock, execute the release lock command:

Mysql> unlock tables;

5. import dump data to Slave

Copy data_dump. SQL to the server load balancer instance through scp.

$ Scp data_dump.sqlmysql @ :/Home/mysql

Use the following command to import data to the MySQL server load balancer instance.

$ Mysql-u root-p

Mysql> source data_dump. SQL;

6. configure replication between the Master and Slave.

Mysql> change master to MASTER_HOST =' ',

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 found in 6.2. Is the Master's IP address or host name.

Start Slave:

Mysql> start slave;

7. verify the status of Master and Slave

Run the following command on the Master:

Mysql> show master status;

Run the following command on Slave:

Mysql> show slave status;

The above information shows no error. the Slave_IO_State information is 'waitigfor master to send event', indicating that the replication connection configuration is OK.

Now, the Master/Slave clusters of two MySQL instances have been deployed successfully. you can perform data update on the Master and copy them to the Slave normally.