MySQL multi-instance-master-slave copy installation

Installation Environment: Centos6.5

MySQL version: mysql-5.5.32.tar.gz

One: Prepare before installation:

1. Install some dependent libraries

Yum install cmake gcc gcc-c++ ncurses-devel Bison perl zilb-devel libio-devel perl-devel-y

2. Create a MySQL user and user group

[Emailprotected] ~ ~]# useradd-~]# id Mysqluid=501(mysql) gid=501( MySQL) group =501(MySQL)

3. Create a MySQL data storage directory, set up two instances of installation 3306,3307

[Email protected] ~]# mkdir-p/data/{3306,3307}/~]# tree/data/   33063307    └──data

4. Compiling the installation

Tar zxvf mysql-5.5.32.tar.gz
CD MySQL-5.5.  +  -dcmake_install_prefix=/usr/local/mysql-dmysql_datadir=/data/mysql-ddefault-charset=utf8-dmysql_ user=mysqlmakemake Install

5. Copy the MY.CNF configuration file and MySQL boot script to/data/3306 and 3307 respectively.

[[email protected] support-files]# cp my-huge.cnf/data/3306/my.cnf[[email protected]support-files ]# CP my-huge.cnf/data/3307/my.cnf[[email protected]support-files]# CP mysql.server/data/3306 /Mysql[[email protected]support-files]# CP mysql.server/data/3307/mysql

6. Modify the/data directory owner

[Email protected] ~]# chown-r mysql:mysql/data

7. Modify the configuration file for the 3306,3307 instance

Take 3306 example, 3307 modify the corresponding location content
# The following options would be passed to all MySQL Clients[client] #password=Your_passwordport=3306Socket=/data/3306/Mysql.sock# The MySQL server[mysqld]Port = 3306socket =/data/3306/ mysql.sockSkip-external-lockingkey_buffer_size=384mmax_allowed_packet=1mtable_open_cache= +sort_buffer_size=2mread_buffer_size=2mread_rnd_buffer_size=8mmyisam_sort_buffer_size=64mthread_cache_size=8query_cache_size=32m# Try number of CPU's*2 for Thread_concurrencyThread_concurrency =8Datadir=/data/3306/data 
....... Skip a paragraph .....

Log-bin=mysql-bin #开启二进制日志, no binary logs are required from the server, 3307 instances comment out this line

Server-id = 1 #实例的server-id cannot be the same, you can set 3307 Server-id = 2

Add [Mysqld_safe] segment at the end of the my.cnf file, define error log files for each instance, PID process location, etc.

[Mysqld_safe]

Log-error=/data/3306/3306.err
Pid-file=/data/3306/mysqld.pid

8. Modify the Startup script file contents of the 3306,3307 instance

1. Locate the basedir= and datadir= two lines, followed by the MySQL installation directory path and data storage path
Basedir=/usr/local/mysqldatadir=/data/3306/data

2. Navigate To Line 283:

$bindir/mysqld_safe --datadir= "$datadir "--pid-file= "$mysqld _pid_file_path" $other _args >/dev/null 2 >&1 &

Switch

$bindir/mysqld_safe --datadir= "$datadir/my.cnf" --pid-file= "$mysqld _pid_file_path" $other _args >/dev/ Null 2>&1 &

3307 instances also modify the corresponding location

9. Execute permissions on the startup script for 3306,3307

3306 755  3307755

10. Initializing the database

[Email protected] ~]# cd/usr/local/mysql/scripts/[[email protected] scripts]#. /mysql_install_db--user=mysql--basedir=/usr/local/mysql/--datadir=/data/3306/data[[email protected ] scripts]#. /mysql_install_db--user=mysql--basedir=/usr/local/mysql/--datadir=/data/3307/data
Go to the data store directory to see

[Email protected] ~]# cd/data/3306/data/#同样查看3307
[[email protected] data]# ls
MySQL Performance_schema test
[Email protected] data]#

11. Start MySQL

Starting 3306 Instances

[Email protected] ~]#/usr/local/mysql/bin/mysqld_safe--defaults-file=/data/3306/my.cnf 2>&1 >/dev/null &
[1] 6083

Take a look
[Email protected] ~]# NETSTAT-TUNLP | grep mysqld
TCP 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 6372/mysqld
Starting 3307 Instances

[Email protected] ~]#/usr/local/mysql/bin/mysqld_safe--defaults-file=/data/3307/my.cnf 2>&1 >/dev/null &
[2] 6393
[Email protected] ~]# NETSTAT-TNLP | grep mysqld
TCP 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 6670/mysqld
TCP 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 6372/mysqld
Started successfully.

And look at the/data directory.

12. Set password hint error to database-bash:mysqladmin:command not found

3306 ]# mysqladmin-3306

The execution instruction system by default is to look for the calling instruction under the/usr/sbin, the/usr/local/mysql/bin/under the Common management command creates the soft connection to/usr/sbin, later uses the MySQL instruction to need not to use the absolute path.

3306] # ln-s/usr/local/mysql/bin/mysqladmin/usr/sbin/3306]# ln-s/usr/local/mysql/bin/mysqld_safe/usr /sbin/
[Email protected] 3306]# ln-s/usr/local/mysql/bin/mysql/usr/sbin/

Set Password

3306] # mysqladmin-u root password root123-s/data/3306/3306]# mysqladmin-u root password root123 -s/data/3307/mysql.scok

An error occurred in the login database and the login was denied.

3306] # mysql-uroot-p-s/data/3306/1045 (28000for'root '@'localhost' (using password:yes)
Login denied ... This makes me depressed for a while ...
Skip the permission table to log in, and then change the password

Mysqld_safe--defaults-file=/data/3306/my.cnf--skip-grant-table &

Re-Login

Mysql-uroot-s/data/3306/mysql.sock

[Email protected] 3306]# mysql-uroot-s/data/3306/mysql.sock
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 1
Server Version:5.5.32-log Source Distribution

Copyright (c) and/or, Oracle, its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

Mysql> select User,password,host from Mysql.user;
+------+----------+-----------+
| user | password | Host |
+------+----------+-----------+
| Root | | localhost |
| Root | | Oracle |
| Root | | 127.0.0.1 |
| Root | | :: 1 |
| | | localhost |
| | | Oracle |
+------+----------+-----------+
6 rows in Set (0.03 sec)

(Depressed: The password set earlier here is why the query is empty ....) ）

First remove user with null name

mysql> use MySQL;
Reading table information for completion of table and column names
Can turn off this feature to get a quicker startup with-a

Database changed
mysql> Delete from user where user= ';
Query OK, 2 rows affected (0.03 sec)

Change Password

mysql> Update user set Password=password (' root123456 ') where user = ' root ';
Query OK, 4 rows affected (0.04 sec)
Rows Matched:4 Changed:4 warnings:0

After exiting, restart the service, log in successfully (almost every time after the installation of the login will encounter this problem ...) ）

Here is a summary of the multi-instance startup, shutdown, and login instructions:
Start command: Mysqld_safe--defaults-file=/data/3306/my.cnf 2>&1 >/dev/null &
Shutdown instruction: Mysqladmin-s/data/3306/mysql.sock shutdown (with password to specify-u,-P option)
Login command: Mysql-uroot-p-s/data/3306/mysql.sock
Specify instance logon skip permission table:
Skip permissions: Mysqld_safe--defaults-file=/data/3306/my.cnf--skip-grant-tables
Skip Permissions Login: Mysql-uroot  -s/data/3306/mysql.sock

//////////////////////////////////////***************************////////////////////////////////////////////// ////////////////////////////

Similarly, 3307 instances also encountered a login problem, the same way to solve. The following begins the master-slave replication configuration.

Configuration of the main library side:

1. Add a user in the main library for communication between master and slave and authorize

' Backup ' @'192.168.%.%'root123456';

mysql> grant replication Slave on * * to ' backup ' @ ' 192.168.%.% ' identified by ' root123456 ';

2. Check that the main library binary is turned on

' Log_bin ' ; +---------------+-------+| variable_name | Value |+---------------+-------+| Log_bin       | On    |+---------------+-------+1inset (0.01 sec)
View current binary record information again

Mysql> Show master status;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 410 | | |
+------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)

Configuring from the library side

Perform master change from library to configure the master and Slave connection parameters (forgot instruction can be performed in MySQL help master change to view)
mysql> use MySQL;

mysql> change Master to \

master_host= ' 192.168.214.133 ' \
-master_user= ' backup ' \
master_password= ' root123456 ' \
master_log_file= ' mysql-bin.000004 ' \
master_log_pos=410;

Start slave from library

mysql>0 rows affected (0.03  sec) to see if slave is starting up properly

Mysql> Show Slave Status\g
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:192.168.214.133
Master_user:backup
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000004
read_master_log_pos:410
relay_log_file:mysqld-relay-bin.000002
relay_log_pos:253
relay_master_log_file:mysql-bin.000004
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:410
relay_log_space:410
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:
Replicate_ignore_server_ids:
Master_server_id:1
1 row in Set (0.00 sec)

Next, you create a database in the main library and test the master-slave replication feature.

Mysql>show tables;+----------------+| Tables_in_demo |+----------------+| Test |+----------------+1Rowinch Set(0.00sec) MySQL>desc test;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| ID | varchar -) |     YES | |       NULL | || name | varchar -) |     YES | |       NULL | |+-------+-------------+------+-----+---------+-------+2Rowsinch Set(0.00sec) MySQL> INSERT INTO Test values ('344','testname'); Query OK,1Row affected (0.36sec) MySQL>Select* fromtest;+------+----------+| ID | Name |+------+----------+|344| TestName |+------+----------+1Rowinch Set(0.44sec) MySQL>

And look at the information synced from the library.

Mysql> show databases; +--------------------+| Database           |+--------------------+| information_schema | | demo               | | mysql              | | performance_schema | | Test               | +--------------------+5inset (0.00  sec) MySQLselect  from demo.test; +------+----------+| ID   | name     344  | testname |+------+----------+1inset ( 0.01 sec)

You can see the master-slave library information synchronization is also normal.

MySQL Multi-instance-master-slave copy installation