Shulou(Shulou.com)05/31 Report--

Editor to share with you how to install multiple instances and master-slave configuration of Mysql under Linux. I hope you will gain something after reading this article. Let's discuss it together.

When mysql creates multiple instances, it actually points DATA files and SOCK,PORT to different files and ports.

Building MySQL multi-instance environment under Linux

1. Install cmake

[root@mysql local] # yum-y install ncurses-devel gcc-c++

[root@mysql local] # cd / usr/local

[root@mysql local] # tar zxvf cmake-2.8.4.tar.gz

[root@mysql local] # cd cmake-2.8.4

[root@mysql cmake-2.8.4] #. / bootstrap-the following error occurred and the C++ compiler is missing

[root@mysql local] # yum install gcc-c++-can handle the following issues

[root@mysql cmake-2.8.4] # gmake

[root@mysql cmake-2.8.4] # make install

[root@mysql cmake-2.8.4] # / usr/local

two。 Install bison

[root@mysql local] # tar-zxvf bison-2.5.tar.gz

[root@mysql local] # cd bison-2.5

[root@mysql bison-2.5] #. / configure

[root@mysql bison-2.5] # make

[root@mysql bison-2.5] # make install

[root@mysql bison-2.5] # / usr/local

3. Compile and install MySQL package

[root@localhost workspace] # tar xvf mysql-5.5.32.tar.gz

[root@localhost workspace] # cd mysql-5.5.32

Install the first MySQL database

(1) create the required file directory

[root@localhost mysql] # useradd mysql

[root@localhost local] # cd / usr/local/

[root@localhost local] # mkdir mysql

[root@localhost local] # cd mysql/

[root@localhost mysql] # mkdir data

[root@localhost mysql] # mkdir etc

[root@localhost mysql] # mkdir log

[root@localhost mysql] # mkdir / var/log/mysql

[root@localhost mysql] # mkdir / var/run/mysqld

[root@localhost mysql] # chown / var/run/mysqld-R

[root@localhost mysql] # chown mysql.mysql / var/log/mysql-R

[root@mysql mysql] # chown mysql.mysql / usr/local/mysql-R

[root@mysql mysql] # chmod + x / usr/local/mysql-R

(2) configure MySQL source code compilation options

[root@localhost mysql-5.5.32] # cmake\

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql\

-DMYSQL_DATADIR=/usr/local/mysql/data\

-DSYSCONFDIR=/usr/local/mysql/etc\

-DWITH_MYISAM_STORAGE_ENGINE=1\

-DWITH_INNOBASE_STORAGE_ENGINE=1\

-DWITH_MEMORY_STORAGE_ENGINE=1\

-DWITH_READLINE=1\

-DMYSQL_UNIX_ADDR=/tmp/mysqld.sock\

-DMYSQL_TCP_PORT=3306\

-DENABLED_LOCAL_INFILE=1\

-DWITH_PARTITION_STORAGE_ENGINE=1\

-DEXTRA_CHARSETS=all\

-DDEFAULT_CHARSET=utf8\

-DDEFAULT_COLLATION=utf8_general_ci

(3) compile and install

[root@localhost mysql-5.5.32] # make & make install

(4) configure the first MySQL instance

[root@localhost mysql-5.5.32] # cd / usr/local/mysql

[root@localhost mysql] # cp support-files/my-medium.cnf / usr/local/mysql/etc/my.cnf

[root@localhost mysql] # vi / usr/local/mysql/etc/my.cnf

[client]

# password = your_password

Port = 3306

Socket = / usr/local/mysql/mysqld.sock

# Here follows entries for some specific programs

# The MySQL server

[mysqld]

Port = 3306

Socket = / usr/local/mysql/mysqld.sock

Skip-external-locking

Key_buffer_size = 16m

Max_allowed_packet = 1m

Table_open_cache = 64

Sort_buffer_size = 512K

Net_buffer_length = 8K

Read_buffer_size = 256K

Read_rnd_buffer_size = 512K

Myisam_sort_buffer_size = 8m

Datadir=/usr/local/mysql/data

Default-storage-engine=Innodb

Slow-query-log-file=/usr/local/mysql/log/slow.log

Log-error=/usr/local/mysql/log/err.log

Pid-file=/usr/local/mysql/mysql3306.pid

Server_id=1

Character_set_server = utf8

Wait-timeout=30

Max_connections = 512

Log-bin = / usr/local/mysql/log/binlog

Sync_binlog=1

Slow-query-log=1

Long-query-time=1

General-log=1

# general-log-file=/data/mysql/log/dml.log

Lower_case_table_names=1

Log_bin_trust_function_creators=1

Skip-slave-start

Binlog-ignore-db=mysql

Binlog-ignore-db=information_schema

Binlog-ignore-db=performance_schema

Replicate_ignore_db=mysql

Replicate_ignore_db=information_schema

Replicate_ignore_db=performance_schema

Expire-logs-days=10

[root@localhost mysql] # cd / usr/local/mysql/scripts/

[root@localhost scripts] # / mysql_install_db-- user=mysql-- basedir=/usr/local/mysql-- datadir=/usr/local/mysql/data &

[root@localhost bin] # cd / usr/local/mysql/bin

[root@localhost bin] # / usr/local/mysql/bin/mysqld_safe-- defaults-file=/usr/local/mysql/etc/my.cnf &-- safe mode startup

[1] 28869

[root@localhost bin] # 131016 20:07:13 mysqld_safe Logging to'/ usr/local/mysql/data/localhost.localdomain.err'.

131016 20:07:14 mysqld_safe Starting mysqld daemon with databases from / usr/local/mysql/data

Check to see if the service started successfully

[root@localhost bin] # netstat-tlnap | grep mysql

Tcp 0 0 0.0.0.0 3306 0.0.0.015 * LISTEN 29147/mysqld

[root@localhost bin] # ps-ef | grep mysql

If the environment variable after startup is different from that specified in the configuration file my.cnf, check whether any my.cnf file under / etc/ interferes with the setting, delete / etc/my.cnf, and restart the database to see if the specified amount is valid again.

Log in to MySQL and change the root user password

[root@localhost bin] #. / mysqladmin-uroot password 'newpasswd'

[root@localhost bin] #. / mysql-uroot-pnewpasswd

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 2

Server version: 5.5.32-log Source distribution

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

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

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

Mysql > exit

Install the second MySQL database

(1) Clean up configuration information

[root@localhost bin] # cd / usr/local/mysql-5.5.32

[root@localhost mysql-5.5.32] # make clean

[root@localhost mysql-5.5.32] # rm-rf CMakeCache.txt

(2) create the required file directory

[root@localhost mysql-5.5.32] # cd / usr/local/

[root@localhost local] # mkdir mysql3307

[root@localhost local] # cd mysql3307/

[root@localhost mysql3307] # mkdir data

[root@localhost mysql3307] # mkdir etc

[root@localhost mysql3307] # mkdir log

[root@localhost mysql3307] # chown mysql.mysql / usr/local/mysql3307-R

(3) configure the compilation information of the second instance

[root@localhost bin] # cd / usr/local/mysql-5.5.32

[root@mysql mysql-5.5.17] # rm-rf CMakeCache.txt

[root@localhost mysql-5.5.32] # cmake\

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql3307\

-DMYSQL_DATADIR=/usr/local/mysql3307/data\

-DSYSCONFDIR=/usr/local/mysql3307/etc\

-DWITH_MYISAM_STORAGE_ENGINE=1\

-DWITH_INNOBASE_STORAGE_ENGINE=1\

-DWITH_MEMORY_STORAGE_ENGINE=1\

-DWITH_READLINE=1\

-DMYSQL_UNIX_ADDR=/tmp/mysqld3307.sock\

-DMYSQL_TCP_PORT=3307\

-DENABLED_LOCAL_INFILE=1\

-DWITH_PARTITION_STORAGE_ENGINE=1\

-DEXTRA_CHARSETS=all\

-DDEFAULT_CHARSET=utf8\

-DDEFAULT_COLLATION=utf8_general_ci

(4) compile and install

[root@localhost mysql-5.5.32] # make & make install

(5) configure the second MySQL instance

[root@localhost mysql-5.5.32] # cd / usr/local/mysql3307

[root@localhost mysql3307] # cp support-files/my-medium.cnf / usr/local/mysql3307/etc/my.cnf

[root@localhost mysql3307] # vi / usr/local/mysql3307/etc/my.cnf

[client]

Port = 3307

Socket = / usr/local/mysql3307/mysqld3307.sock

# Here follows entries for some specific programs

# The MySQL server

[mysqld]

Port = 3307

Socket = / usr/local/mysql3307/mysqld3307.sock

Skip-external-locking

Key_buffer_size = 16m

Max_allowed_packet = 1m

Table_open_cache = 64

Sort_buffer_size = 512K

Net_buffer_length = 8K

Read_buffer_size = 256K

Read_rnd_buffer_size = 512K

Myisam_sort_buffer_size = 8m

Datadir=/usr/local/mysql3307/data

Default-storage-engine=Innodb

Slow-query-log-file=/usr/local/mysql3307/log/slow.log

Log-error=/usr/local/mysql3307/log/err.log

[root@localhost mysql3307] # cd / usr/local/mysql3307/scripts/

[root@localhost scripts] # / mysql_install_db-- user=mysql-- basedir=/usr/local/mysql3307-- datadir=/usr/local/mysql3307/data &

[root@localhost scripts] # cd / usr/local/mysql3307/bin

[root@localhost bin] # / usr/local/mysql3307/bin/mysqld_safe-- defaults-file=/usr/local/mysql3307/etc/my.cnf &

131016 20:40:27 mysqld_safe Logging to'/ usr/local/mysql3307/data/localhost.localdomain.err'.

131016 20:40:27 mysqld_safe Starting mysqld daemon with databases from / usr/local/mysql3307/data

Check to see if the service started successfully

[root@localhost bin] # netstat-tlnap | grep mysql

Tcp 0 0 0.0.0.0 3306 0.0.0.015 * LISTEN 29147/mysqld

Tcp 0 0 0.0.0.0 3307 0.0.0.015 * LISTEN 7447/mysqld

Log in to MySQL and change the root user password

[root@localhost bin] #. / mysqladmin-uroot password 'eisoo.com123'

[root@localhost bin] #. / mysql-uroot-peisoo.com123

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 2

Server version: 5.5.32-log Source distribution

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

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

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

Mysql > exit

Increase system environment variables

[root@localhost /] # vi / etc/profile

Add one line: export PATH=/usr/local/mysql/bin:/usr/local/mysql3307/bin:$PATH

[root@localhost /] # source / etc/profile

[root@localhost /] # export $PATH

Start different instances:

[root@localhost /] # / usr/local/mysql/bin/mysqld_safe-- defaults-file=/usr/local/mysql/etc/my.cnf &

[root@localhost /] # / usr/local/mysql3307/bin/mysqld_safe-- defaults-file=/usr/local/mysql3307/etc/my.cnf &

Log in to different instances:

[root@localhost /] # mysql-uroot-pnewpasswd-S / tmp/mysqld.sock

[root@localhost /] # mysql-uroot-pnewpasswd-S / tmp/mysqld3307.sock

It's still too troublesome to start, you can do this:

[root@localhost /] # ln-s / usr/local/mysql/support-files/mysql.server / etc/init.d/mysql3306

[root@localhost /] # ln-s / usr/local/mysql3307/support-files/mysql.server / etc/init.d/mysql3307

Add permissions to the mysql user:

[root@localhost /] # chmod-R 755 / usr/local/mysql/data

[root@localhost /] # chmod-R 755 / usr/local/mysql3307/data

Start the services corresponding to the instance:

[root@localhost tmp] # service mysql3306 start

Starting MySQL. [OK]

[root@localhost tmp] # service mysql3307 start

Starting MySQL. [OK]

View services:

[root@localhost tmp] # netstat-tlnap | grep mysql

Tcp 0 0 0.0.0.0 3306 0.0.0.015 * LISTEN 10759/mysqld

Tcp 0 0 0.0.0.0 3307 0.0.0.015 * LISTEN 11097/mysqld

Add the service to the system service to make it boot automatically.

[root@localhost /] # chkconfig-- add mysql3306

[root@localhost /] # chkconfig-- add mysql3307

Smooth shutdown of multiple instances:

[root@localhost /] # mysqladmin-u root-p-S-- socket=/usr/local/mysql/mysqld3306.sock shutdown

[root@localhost /] # mysqladmin-u root-p-S-- socket=/usr/local/mysql3307/mysqld3307.sock shutdown

II. Master-slave configuration of MySQL

1. Modify the my.cnf file of master database

# vi / etc/my.cnf

[mysqld]

Basedir = / data/mysql

Datadir = / data/mysql/data

Port = 3306

Server_id = 1

Socket = / data/mysql/mysql.sock

Pid-file=/data/mysql/mysql.pid

Character_set_server = utf8

Wait-timeout=30

Max_connections = 512

Default-storage-engine = Innodb

Log-bin = / data/mysql/log/binlog

Sync_binlog=1

Slow-query-log=1

Long-query-time=1

Slow-query-log-file=/data/mysql/log/slow.log

Log-error=/data/mysql/log/err.log

General-log=1

General-log-file=/data/mysql/log/dml.log

Lower_case_table_names=1

Log_bin_trust_function_creators=1

Skip-slave-start

Binlog-ignore-db=mysql

Binlog-ignore-db=information_schema

Binlog-ignore-db=performance_schema

Replicate_ignore_db=mysql

Replicate_ignore_db=information_schema

Replicate_ignore_db=performance_schema

Expire-logs-days=10

[mysql.server]

User=mysql

Basedir=/data/mysql

[client]

Socket=/data/mysql/mysql.sock

(note: 1. After modification, starting the database can fail because the folder does not exist, or the folder permissions cannot be written)

# mkdir / data/mysql/log & & chown mysql.mysql / data/mysql-R

two。 If there is no [client] condition, an error ERROR 2002 (HY000): Can't connect to local MySQL server through socket'/ var/lib/mysql/mysql.sock' (2) will be reported at startup

Start the master main library

# service mysql start

Starting MySQL. SUCCESS!

2. Modify the configuration file of slave slave library (/ etc/my.cnf)

# vi / etc/my.cnf

[mysqld]

Basedir = / data/mysql

Datadir = / data/mysql/data

Port = 3306

Server_id = 3-- different from the main database

Socket = / data/mysql/mysql.sock

Pid-file=/data/mysql/mysql.pid

Character_set_server = utf8

Wait-timeout=30

Max_connections = 512

Default-storage-engine = Innodb

Log-bin = / data/mysql/log/binlog

Sync_binlog=1

Log-error=/data/mysql/log/err.log

Relay-log-index = / data/mysql/relaylog/relaylogindex

Relay-log-info-file = / data/mysql/relaylog/relayloginfo

Relay-log = / data/mysql/relaylog/relaylog

Slow-query-log=1

Long-query-time=1

Slow-query-log-file=/data/mysql/log/slow.log

Log-error=/data/mysql/log/err.log

General-log=1

General-log-file=/data/mysql/log/dml.log

Lower_case_table_names=1

Log_bin_trust_function_creators=1

Binlog-ignore-db=mysql

Binlog-ignore-db=information_schema

Binlog-ignore-db=performance_schema

Replicate_ignore_db=mysql

Replicate_ignore_db=information_schema

Replicate_ignore_db=performance_schema

Expire-logs-days=10

Read-only

[mysql.server]

User=mysql

Basedir=/data/mysql

[client]

Socket=/data/mysql/mysql.sock

(note: 1. After modification, starting the database can fail because the folder does not exist, or the folder permissions cannot be written.

# mkdir / data/mysql/relaylog/ & & # mkdir / data/mysql/log/ & & # chown mysql.mysql / data/mysql/-R

two。 If there is no [client] condition, an error ERROR 2002 (HY000): Can't connect to local MySQL server through socket'/ var/lib/mysql/mysql.sock' (2) will be reported at startup

3.master database lock table (so that there is no data operation interference when exporting the library & get binary coordinates)

The login password of the user authorizing master-slave replication is' longshine' 'longshine'

Mysql > grant replication slave,replication client on *. * to longshine@'192.168.81.121' identified by 'longshine'

Query OK, 0 rows affected (0.22 sec)

Locking tables facilitates down data

Mysql > flush tables with read lock;-lock the master database table to keep the consistency between master and slave

Query OK, 0 rows affected (0.05 sec)

Export data

# mysqldump-uroot-p-all-databases > > / home/mysql/all.sql

Enter password: # #-(enter password)

Import data from the library

# / usr/local/mysql3307/bin/mysql-uroot-p-- socket=/usr/local/mysql3307/mysqld3307.sock

< /home/mysql/all.sql Enter password: ##-----（输入密码） 查看主库的二进制日志状态 mysql>

Show master status

Mysql > show master status\ G

* * 1. Row *

File: binlog.000004

Position: 335

Binlog_Do_DB:

Binlog_Ignore_DB: mysql,information_schema,performance_schema

Executed_Gtid_Set:

1 row in set (0.00 sec)

The slave library synchronizes with the master library according to the binary log status setting of the master library.

Mysql > change master to master_host = '192.168.81.14 mastery mastery portals 3306 mastery usernames longshinebooks binlog.000004',master_log_pos=335

4. Unlock the main library

Login execution of main library

Mysql > unlock tables

Start the copy thread from the library

Mysql > start slave

Query OK, 0 rows affected (0.01 sec)

View copy status from the library

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.81.13

Master_User: longshine

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: binlog.000004

Read_Master_Log_Pos: 120

Relay_Log_File: relaylog.000007

Relay_Log_Pos: 280

Relay_Master_Log_File: binlog.000004

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB: mysql,information_schema,performance_schema

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: 120

Relay_Log_Space: 606

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

Master_UUID: d67ab0e7-6044-11e5-8147-000c299db641

Master_Info_File: / data/mysql/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

(note: if Slave_IO_Running: connecting is displayed, first check whether there is a problem with the permission of the master-slave transfer user "longshine" in the master database, then close the slave database thread, modify the master database user rights, and restart the database)

Rebuild standby database for error handling:

160402 9:44:24 [ERROR] Failed to open the relay log'. / mysql-relay-bin.000005' (relay_log_pos 1001920)

160402 9:44:24 [ERROR] Could not find target log during relay log initialization

160402 9:45:16 [ERROR] Failed to open the relay log'. / mysql-relay-bin.000005' (relay_log_pos 1001920)

160402 9:45:16 [ERROR] Could not find target log during relay log initialization

160402 9:45:58 [ERROR] Failed to open the relay log'. / mysql-relay-bin.000005' (relay_log_pos 1001920)

160402 9:45:58 [ERROR] Could not find target log during relay log initialization

The analysis should be due to the fact that the paths to the old relay log files are still saved in mysql-relay-bin.index, and no suitable files can be found under these paths, so an error is reported.

It is relatively simple to solve this kind of problem, just reset the reference to slave, and execute the command as follows:

Mysql > reset slave

Query OK, 0 rows affected (0.00 sec)

Mysql > change master to master_host = '192.168.81.121 famous masterminds portals 3306 masterful usernames longshinebooks pagehands masterbooks passwordbooks longshinebooks filebooks books mysqlhands bin.000002masterpieces logbooks possessions 9187015

Query OK, 0 rows affected (0.21 sec)

Mysql > start slave

Query OK, 0 rows affected (0.02 sec)

After reading this article, I believe you have a certain understanding of "how to install multiple instances and master-slave configuration of Mysql under Linux". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.