Single server MySQL master-slave Replication Practice

First, the configuration of two MySQL instances and can start normally

1. First install MySQL database on Linux, omit detailed installation steps here (can be installed with Yum)

Yum-y install mysqld MySQL

2. Create a new directory under the /usr/local directory MYSQLS/MYSQL1, mysqls/mysql2 respectively as our master data and from the database directory, new Mysqls/run as MySQL PID storage directory, new mysqls/ Log as MySQL log directory

Mkdir-p/USR/LOCAL/MYSQLS/MYSQL1/USR/LOCAL/MYSQLS/MYSQL2
Mkdir-p/usr/local/mysqls/run/usr/local/mysqls/log

3. copy the MySQL default configuration file/etc/my.cnf to/usr/local/mysqls, and copy two copies named Mysql1.cnf and MYSQL2.CNF respectively. These two files will be used as the master-slave MySQL database configuration file (this way, can not affect the original MySQL configuration, the default MySQL will still work)

Cp/etc/my.cnf/usr/local/mysqls/mysql1.cnf
Chown-r Mysql:mysql
Cd/usr/local/mysqls
CP mysql1.cnf MYSQL2.CNF

4. Modify the MySQL instance configuration file, specify the path of DataDir, socket, port, Log-error, Pid-file:

The MYSQL1.CNF configuration is as follows:

[Mysqld]
Datadir=/usr/local/mysqls/mysql1
Socket=/usr/local/mysqls/mysql1/mysql.sock
port=33061
User=mysql
# Disabling Symbolic-links is recommended to prevent assorted security risks
Symbolic-links=0
#default-character-set=utf8
Character-set-server=utf8
[Mysqld_safe]
Log-error=/usr/local/mysqls/log/mysql1.log
Pid-file=/usr/local/mysqls/run/mysql1.pid
[Client]
Default-character-set=utf8
Socket=/usr/local/mysqls/mysql1/mysql.sock

MYSQL2.CNF refer to above configuration, use Mysql2 directory, port number set to 33062

5. Next use the mysql_install_db script to initialize the two MySQL DB instances (the script will automatically install the MySQL base database in the MYSQL1 and MYSQL2 directories: MySQL)

/usr/bin/mysql_install_db--defaults-file=/usr/local/mysqls/mysql1.cnf
/usr/bin/mysql_install_db--defaults-file=/usr/local/mysqls/mysql2.cnf

6. In order to launch the instance every time, we can write the following startup script that uses Mysqld_safe to start the MySQL instance:

Start_mysql1.sh content is as follows:

#/bin/bash
No=1
Exe=/usr/bin/mysqld_safe
Madmin=/usr/bin/mysqladmin
Base=/usr/local/mysqls
Sub=mysql$no
conf= $base/$sub. cnf
socket= $base/$sub/mysql.sock
pidfile= $base/run/$sub. pid
Port=3306$no
Timeout=60

$exe--defaults-file= $conf 2>&1 &
While [$timeout-GT 0]; Do
$madmin--socket= "$socket"--user=root--port= $port Ping >/dev/nul 2>&1
Mret=$?
If [$mret-eq 0]; Then
now= ' date + '%y-%m-%d%h:%m:%s '
echo "[$now] Start $sub: OK"
echo "[$now] PID: ' Cat $pidfile '"
NETSTAT-ATULNP | grep $port
Break
Fi
Sleep 1
Let Timeout=${timeout}-1
Done

Start_mysql1.sh is used to start the MYSQL1, similarly we can write start_mysql2.sh for starting MYSQL2 (change the value of no variable to 2)

Note: The shell script file needs to be set executable permissions before execution (similar to this: Chown u+x start_mysql1.sh)

7. So far, the preparation of MySQL two instances is ready, we can try to start them (take MYSQL1 as an example)

/usr/local/mysqls/start_mysql1.sh

If all goes well, the following results will appear:

Second, we start to set up master-slave replication

1. Stop the MySQL instance and write the stop script to facilitate the same

Stop_mysql.sh content is as follows:

#/bin/bash

If [$#-ne 1]; Then
echo "Usage: $ <mysql no>"
Exit-1
Fi

No=$1
Sub=mysql$no
Base=/usr/local/mysqls
pidfile= $base/run/$sub. pid

if [!-F "$pidfile"]; Then
echo "' $pidfile ' not exist, MySQL server could not start"
Exit-2
Fi
Pid= ' Cat $pidfile '
Kill $pid

Execution: stop_mysql.sh 1 to stop the MYSQL1, after the parameter changed to 2 can stop MYSQL2, as follows:

2. Configure the primary database, turn on the replication function (here use MYSQL1 as the primary database, MYSQL2 as the slave database)

The following configuration is added below the [mysqld] tag in mysql1.cnf:

[Mysqld]
...
#replication
Log-bin=/usr/local/mysqls/log/mysql1-bin.log # This is the location of MySQL bin log
Server-id=1 # server ID, identifying MySQL
# The following two parameters indicate ensuring maximum persistence and consistency of the InnoDB transactional database
Innodb_flush_log_at_trx_commit=1
Sync_binlog=1
# Additional parameters, we can set the copy which library, which table, do not copy which library, which table (not detailed here, the specific reference MySQL official document)
For example
# Replicate-do-db=mydb
# replicate-do-table=mytable
# Replicate-ignore-db=mysql
# Replicate-ignore-table=user

3. Start the Mysql1,mysql command to connect and add the replication account while viewing the master status

start_mysql1.sh
Mysql -s /usr /local/msyqls/mysql1.sock -uroot -p
Mysql> grant all on *.* to rep ' @ ' localhost ' identified by  ' Replpass ';  #  the actual environment here according to their own needs of the
Mysql> flush privileges;
Mysql> show master status \g
*************************** 1. row *****
           File:  mysql1-bin.000002
       position: 1486
   Binlog _do_db: 
binlog_ignore_db: 
1 row in set  (0.00 sec)

4. If the master-slave database is completely new (no actual data) can not be backed up from the main library to the slave library, otherwise you need to export the main library data to from the library

# Set read lock (write forbidden)
Mysql> flush tables with read lock;
# Perform a backup operation
/usr/bin/mysqldump-uroot-p--database mydb1, mydb2 ... > Dump.sql
#解锁

mysql> unlock tables;

?

5. Configure replication parameters from the database

The following configuration is added under the mysqld tag in mysql2.cnf:

#replication
Log-bin=/usr/local/mysqls/log/mysql2-bin.log
server-id=2
Master-host=localhost # Main Library Address
master-port=33061 # Main Library port number
Master-user=rep # Copy Account set above
Master-pass=somepass # Copy account password

MASTER-CONNECT-RETRY=60 # Retry the connection time interval

6. Start from the database, if the main library has data to import then first import the backup, then set the replication start location, start slave

start_mysql2.sh
Mysql-s/usr/local/mysqls/mysql2/mysql.sock-uroot-p < Dump.sql
Mysql-s/usr/local/mysqls/mysql2/mysql.sock-uroot-p
Mysql> Change Master to
master_log_file=mysql1-bin.000002
master_log_pos=1486;
mysql> start slave;
Mysql> Show Slave status \g
1. Row ***************************
Slave_io_state:waiting for Master to send event
Master_host:localhost
Master_user:rep
master_port:33061
Connect_retry:60
master_log_file:mysql1-bin.000002
read_master_log_pos:1486
relay_log_file:mysql2-relay-bin.000002
relay_log_pos:252
relay_master_log_file:mysql1-bin.000002
Slave_io_running:yes
Slave_sql_running:yes
replicate_do_db:
replicate_ignore_db:
Replicate_do_table:
Replicate_ignore_table:
Replicate_wild_do_table:
Replicate_wild_ignore_table:
last_errno:0
Last_error:
skip_counter:0
exec_master_log_pos:106
relay_log_space:554
Until_condition:none
Until_log_file:
until_log_pos:0
Master_ssl_allowed:no
Master_ssl_ca_file:
Master_ssl_ca_path:
Master_ssl_cert:
Master_ssl_cipher:
Master_ssl_key:
seconds_behind_master:0
Master_ssl_verify_server_cert:no
last_io_errno:0
Last_io_error:
last_sql_errno:0
Last_sql_error:
1 row in Set (0.00 sec)

7. OK, MySQL two instance master-slave copy is built.

Note: It is necessary to specify the socket file when using the MySQL command, or to write a script like this to avoid the need to add this parameter to each connection.

Mysql1.sh content is as follows:

#/bin/bash
No=1
base=/usr/local/mysqls/
Sub=mysql$no
socket= $base/$sub/mysql.sock
Mysql-s $socket $*

Mysql2.sh changes the value of No to 2

Single server MySQL master-slave Replication Practice