mysql5.的编译安装单机多实例

MySQL单机多实例有两个方案

安装依赖:

yum -y install gcc gcc-c++ make autoconf libtool-ltdl-devel gd-devel freetype-devel libxml2-devel libjpeg-devel libpng-devel openssl-devel curl-devel bison patch unzip libmcrypt-devel libmhash-devel ncurses-devel sudo bzip2 flex libaio-devel

yum -y install cmake

下载MySQL的tar包 带boost的MySQL源码包 

 解压并用cmake编译

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql/  -DWITH_BOOST=./boost

make && make install

     创建MySQL用户组和用户以及数据存放目录

 mkdir -p /data/{3306,3307}/data groupadd mysql useradd -r -g mysql -s /bin/false mysql chown -R mysql:mysql /data

初始化两个实例

/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data --initialize-insecure

/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/3307/data --initialize-insecure

多配置文件启动 MySQL客户端链接 tcp链接以及MySQL服务的关闭原理

启动原理

mysqld_safe --defaults-file=/data/3306/my.cnf 2>&1 > /dev/null &

客户端链接

mysql -uroot -S sock文件路径 -p

tcp连接

mysql -uroot -pxxx -P3306

MySQL的关闭

mysqladmin -uroot -p -S sock文件路径 shutdown

给出一个配置文件放在3306目录下  3307的自己修改   切记配置文件的权限不要777 最好644

[client]
port = 3306
socket = /data/3306/mysql.sock
character-set-server = utf8
  
[mysql]
no-auto-rehash
  
[mysqld]
user    = mysql
port    = 3306
character-set-server = utf8
socket  = /data/3306/mysql.sock
basedir = /opt/application/mysql
datadir = /data/3306/data
explicit_defaults_for_timestamp = true
skip-ssl
secure-file-priv = NULL
lower_case_table_names = 1
back_log = 300
max_connections = 3000
max_connect_errors = 100
table_open_cache = 4096
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 16M
join_buffer_size = 16M
thread_cache_size = 16
query_cache_size = 128M
query_cache_limit = 4M
ft_min_word_len = 8
thread_stack = 512K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 128M
max_heap_table_size = 128M
  
###*** slow query parameters
long_query_time = 6
slow_query_log
slow_query_log_file = /data/3306/slow.log
  
###*** binlog parameters
log-bin = /data/3306/mysql-bin
binlog_cache_size = 4M
max_binlog_cache_size = 16M
max_binlog_size = 32M
binlog_format = MIXED
expire_logs_days = 7
  
###*** relay-log parameters
#relay-log = /data/3307/relay-bin
#relay-log-info-file = /data/3307/relay-log.info
#master-info-repository = table
#relay-log-info-repository = table
#relay-log-recovery = 1
  
#*** MyISAM parameters
key_buffer_size = 16M
read_buffer_size = 1M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 1M
  
#skip-name-resolve
  
###*** master-slave replication parameters
server-id = 105
slave-skip-errors = all
  
#*** Innodb storage engine parameters
innodb_buffer_pool_size = 6G
innodb_data_file_path = ibdata1:10M:autoextend
#innodb_file_io_threads = 8
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 16M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = on
  
[mysqldump]
quick
max_allowed_packet = 32M
  
[myisamchk]
key_buffer = 16M
sort_buffer_size = 16M
read_buffer = 8M
write_buffer = 8M
  
[mysqld_safe]
open-files-limit = 8192
#log-error=/data/3306/mysql_3306.err 这个存在bug 必须用MySQL用户登陆执行 或者手动创建
pid-file=/data/3306/mysqld.pid

单配置文件启动 MySQL客户端链接 tcp链接以及MySQL服务的关闭原理

 使用MySQL根目录的 support-files/mysqld_multi.server  [start|stop|report|restart]

./mysqld_multi.server start 3306  启动一个  启动时报错 需要把 /usr/local/mysql/bin 加入环境变量  netstat -ntlp  | grep mysql 查看启动状态

./mysqld_multi.server start 3306-3307 启动多个

./mysqld_multi.server stop 3306 

给配置文件 放在 /etc/my.cnf 

https://dev.mysql.com/doc/refman/5.7/en/mysqld-multi.html     MySQL官方配置

[mysqld_multi] mysqld = /usr/local/mysql/bin/mysqld_safe mysqladmin = /usr/local/mysql/bin/mysqladmin user = multi_admin password   = my_passw                      
[mysqld3306] character-set-server = utf8 socket     = /tmp/mysql3306.sock port       = 3306 pid-file   = /usr/local/lnmp/mysql/data/3306/3306.pid datadir    = /usr/local/lnmp/mysql/data/3306/data basedir    = /usr/local/lnmp/mysql server-id  = 3306 ###*** slow query parameters long_query_time = 6 slow_query_log slow_query_log_file = /usr/local/lnmp/mysql/data/3306/slow.log
 ###*** binlog parameters log-bin = /usr/local/lnmp/mysql/data/3306/mysql-bin binlog_cache_size = 4M max_binlog_cache_size = 16M max_binlog_size = 32M binlog_format = MIXED expire_logs_days = 7
[mysqld3307] character-set-server = utf8 socket     = /tmp/mysql3307.sock port       = 3307 pid-file   = /usr/local/lnmp/mysql/data/3307/3307.pid datadir    = /usr/local/lnmp/mysql/data/3307/data basedir    = /usr/local/lnmp/mysql server-id  = 3307 ###*** slow query parameters long_query_time = 6 slow_query_log slow_query_log_file = /usr/local/lnmp/mysql/data/3307/slow.log
 ###*** binlog parameters # log-bin = /usr/local/lnmp/mysql/data/3306/mysql-bin #binlog_cache_size = 4M #max_binlog_cache_size = 16M #max_binlog_size = 32M #binlog_format = MIXED #expire_logs_days = 7
 ###*** relay-log parameters relay-log = /usr/local/lnmp/mysql/data/3307/relay-bin relay-log-info-file = /usr/local/lnmp/mysql/data/3307/relay-log.info master-info-repository = table relay-log-info-repository = table relay-log-recovery = 1