单机装两个mysql_MySQL单机上多实例安装

首先安装mysql，不要启动MySQL，先配置vim /etc/my.cnf。

[mysqld_multi]

mysqld = /usr/bin/mysqld_safe

mysqladmin = /usr/bin/mysqladmin

log = /opt/testdb/logs/mysql/mysqld_multi/mysqld_multi.log

[mysqld1]

datadir=/opt/testdb/mysql3306

socket=/opt/testdb/mysql3306/mysql.sock

symbolic-links=0

skip-name-resolve

default-time-zone='+8:00'

port = 3306

secure_file_priv = /opt/testdb/mysql_data

innodb_file_per_table=1

innodb_open_files=1024

skip-external-locking

net_buffer_length = 8K

myisam_sort_buffer_size = 8M

lower_case_table_names = 1

wait_timeout=1800

interactive_timeout = 1800

key_buffer_size = 512M

back_log = 500

max_connections = 3000

max_connect_errors = 6000

table_open_cache = 2048

max_allowed_packet = 32M

join_buffer_size = 2M

thread_cache_size= 128

query_cache_limit = 8M

tmp_table_size = 256M

read_buffer_size=8M

sort_buffer_size=8M

bulk_insert_buffer_size = 64M

innodb_buffer_pool_size = 102400M

innodb_buffer_pool_instances = 8

innodb_log_buffer_size=20M

read_rnd_buffer_size=32M

slow_query_log = 1

slow_query_log_file = /opt/testdb/logs/mysql/3306/mysqld3306_slow.log

long_query_time = 10

log_queries_not_using_indexes = 1

log_output = 'FILE'

innodb_flush_log_at_trx_commit = 2

sync_binlog=8

log-error=/opt/testdb/logs/mysql/3306/mysqld3306.log

pid-file=/var/run/mysql/mysqld3306.pid

server-id=36

binlog_ignore_db=mysql

relay_log = relay-bin

log-bin=/opt/testdb/logs/mysql/3306/mysql_R3306.log

[mysqld2]

datadir=/opt/testdb/mysql3307

socket=/opt/testdb/mysql3307/mysql.sock

symbolic-links=0

skip-name-resolve

default-time-zone='+8:00'

port = 3307

secure_file_priv = /opt/testdb/mysql_data

innodb_file_per_table=1

innodb_open_files=1024

skip-external-locking

net_buffer_length = 8K

myisam_sort_buffer_size = 8M

lower_case_table_names = 1

wait_timeout=1800

interactive_timeout = 1800

key_buffer_size = 512M

back_log = 500

max_connections = 3000

max_connect_errors = 6000

table_open_cache = 2048

max_allowed_packet = 32M

join_buffer_size = 2M

thread_cache_size= 128

query_cache_limit = 8M

tmp_table_size = 256M

read_buffer_size=8M

sort_buffer_size=8M

bulk_insert_buffer_size = 64M

innodb_buffer_pool_size = 20480M

innodb_log_buffer_size=20M

read_rnd_buffer_size=32M

slow_query_log = 1

slow_query_log_file = /opt/testdb/logs/mysql/3307/mysqld3307_slow.log

long_query_time = 10

log_queries_not_using_indexes = 1

log_output = 'FILE'

innodb_flush_log_at_trx_commit = 2

sync_binlog=8

log-error=/opt/testdb/logs/mysql/3307/mysqld3307.log

pid-file=/var/run/mysql/mysqld3307.pid

server-id=37

relay-log = relay-bin

log-slave-updates = 1

binlog_ignore_db=mysql

log-bin=/opt/testdb/logs/mysql/3307/mysql_R3307.log

注意其中innodb_buffer_pool_size配置，这里使用的引擎是Innodb。

在/opt/sdb下新建mysql3306和mysql3307两个文件夹：/opt/testdb/mysql3306， /opt/testdb/mysql3307

分别提权：

chown -R mysql:mysql /opt/testdb/mysql3306

chown -R mysql:mysql /opt/testdb/mysql3307

到mysqld目录下，cd /usr/sbin

初始化，执行

mysqld --initialize --datadir=/opt/testdb/mysql3306 --user=mysql，保存临时密码

同样执行，mysqld --initialize --datadir=/opt/testdb/mysql3307 --user=mysql，保存临时密码

运行MySQL，mysqld_multi start 1-2，同时启动

登陆MySQL，修改密码，mysql -uroot -p -S /opt/testdb/mysql3306/mysql.sock (或者mysql -uroot -p -P3306)

登陆后修改密码：

ALTER USER 'root'@'localhost' identified by '12345678';

FLUSH PRIVILEGES;

添加远程/登陆用户

grant all PRIVILEGES on *.* totestDB@'%' identified by '12345678';

查看MySQL_multi状态，mysqld_multi report

关闭musqld_multi，mysqladmin -h172.16.2.64 -P3306 -uroot -p12345678 shutdown