随着硬件层面的发展,linux系统多核已经是普通趋势,,而mysql是单进程多线程,所以先天上对多进程的利用不是很高,虽然5.6版本已经在这方面改进很多,但是也没有达到100%,所以为了充分的利用系统资源,mysql有自己的补充,那就是可以部署多实例,一个实例一个端口。
源码安装mysql参考blog:,源码包下载地址:
mkdir-p /usr/local/mysql
timecmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-DMYSQL_DATADIR=/home/data/mysql/data -DWITH_INNOBASE_STORAGE_ENGINE=1-DMYSQL_UNIX_ADDR==/usr/local/mysql/mysql.sock -DMYSQL_USER=mysql-DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci
由于time make耗时比较长,所以事先已经安装好
timemake
timemake install
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin =/usr/local/mysql/bin/mysqladmin
log =/home/mysql/mydata/log/mysqld_multi.log
[mysqld1]
socket = /usr/local/mysql3307/mysql.sock
port = 3307
pid-file = /usr/local/mysql3307/mysqld.pid
datadir = /home/data/mysql3307/data
log_bin=/home/data/mysql3307/binlog
server-id = 1230
relay_log =/home/data/mysql3307/relay_log/mysql-relay-bin
log_slave_updates = 1
read_only = 0
innodb_buffer_pool_size = 128M
innodb_flush_log_at_trx_commit = 0
[mysqld2]
socket = /usr/local/mysql3308/mysql.sock
port = 3308
pid-file = /usr/local/mysql3308/mysqld.pid
datadir = /home/data/mysql3308/data
log_bin=/home/data/mysql3308/binlog
server-id = 2230
relay_log =/home/data/mysql3308/relay_log/mysql-relay-bin
log_slave_updates = 1
read_only = 0
innodb_buffer_pool_size = 128M
innodb_flush_log_at_trx_commit = 0
[mysqld3]
socket = /usr/local/mysql3309/mysql.sock
port = 3309
pid-file = /usr/local/mysql3309/mysqld.pid
datadir = /home/data/mysql3309/data
log_bin=/home/data/mysql3309/binlog
server-id = 3230
relay_log = /home/data/mysql3309/relay_log/mysql-relay-bin
log_slave_updates = 1
read_only = 0
innodb_buffer_pool_size = 128M
innodb_flush_log_at_trx_commit = 0
[mysql@data02 ~]$ mysqld_multi–defaults-extra-file=/etc/my.cnf report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running
[mysql@data02 ~]$
后台日志信息:
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running
需要指定启动的socket就可以登录到各自的mysql实例,如下所示:
[mysql@data02 ~]$ mysql –socket=/usr/local/mysql3308/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.12-log Sourcedistribution
Copyright (c) 2000, 2013, Oracle and/or itsaffiliates. All rights reserved.
Oracle is a registered trademark of OracleCorporation and/or its
affiliates. Other names may be trademarksof their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ toclear the current input statement.
mysql> select @@port;
+——–+
| @@port |
+——–+
|3308 |
+——–+
1 row in set (0.00 sec)
mysql>
mysql> create database d3308;
Query OK, 1 row affected (0.04 sec)
mysql>
复制账号
GRANTREPLICATION SLAVE,RELOAD,SUPER ON *.*TO repl@’192.168.52.%’ IDENTIFIED BY’repl_1234′;
备份账号
GRANTRELOAD, SUPER, LOCK TABLES, REPLICATION CLIENT, CREATE TABLESPACE ON *.* TO’backup’@’192.168.%’ IDENTIFIED BY ‘123456’;
GRANTALL PRIVILEGES ON `mysql`.* TO ‘backup’@’192.168.%’;
GRANTEVENT ON *.* TO ‘backup’@’192.168.%’;
备份:
/usr/local/mysql/bin/mysqldump-ubackup –password=123456 –host=192.168.52.138–socket=/usr/local/mysql3307/mysql.sock –port=3307 -R -E –skip-opt–single-transaction –flush-logs –master-data=2 –add-drop-table–create-option –quick –extended-insert=false –set-charset –disable-keys-A > /tmp/alldbfullbackup.sql
导入:
/usr/local/mysql/bin/mysql-uroot -p –socket=/usr/local/mysql3308/mysql.sock –port=3308</tmp/alldbfullbackup.sql
找到复制点
more/tmp/alldbfullbackup.sql
开始建立复制链接
CHANGE MASTER TOMASTER_HOST=’192.168.52.138′,MASTER_PORT=3307,
MASTER_USER=’repl’,
MASTER_PASSWORD=’repl_1234′,
MASTER_LOG_FILE=’mysql-bin.000006′,
MASTER_LOG_POS=120;
开启复制
startslave;
查看复制状态
showslave status\G;
mysql> show slave status\G
*************************** 1. row***************************
Slave_IO_State: Waiting formaster to send event
Master_Host: 192.168.52.138
Master_User: repl
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-bin.000011
Read_Master_Log_Pos: 120
Relay_Log_File:mysql-relay-bin.000005
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000011
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
……
Seconds_Behind_Master: 0
由于多实例中,各个实例的资源都是不share的,所以要合理分配好各个实例的内存、磁盘等资源,避免out of memery或则 full disk的情况出现。
只要功夫深,铁棒磨成绣花针。