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