mysql5.5.49多实例安装

• 创建mysql账户，修改hosts

useradd mysql -s /sbin/nologin -Mvim /etc/hosts127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4 mysql01::1         localhost localhost.localdomain localhost6 localhost6.localdomain6 mysql01

• 下载安装包解压安装包

useradd mysql -s /sbin/nologin -Mmkdir -p  /application/tools/cd /application/tools/wget http://downloads.mysql.com/archives/get/file/mysql-5.5.49.tar.gztar xf mysql-5.5.49.tar.gz cd mysql-5.5.49

• 安装插件

yum -y install cmake ncurses-devel libaio-devel

• cmake编译安装

cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.49 \-DMYSQL_DATADIR=/application/mysql-5.5.49/tmp/mysql.sock \-DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci \ -DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \-DENABLED_LOCAL_INFILE=ON \-DWITH_INNOBASE_STORAGE_ENGINE=1 \-DWITH_FEDERATED_STORAGE_ENGINE=1 \-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \-DWITHOUT_PARTITION_STORAGE_ENGINE=1 \-DWITH_FAST_MUTEXES=1 \-DWITH_ZLIB=bundled -DENABLED_LOCAL_INFILE=1 \-DWITH_READLINE=1 \-DWITH_EMBEDDED_SERVER=1 \-DWITH_DEBUG=0make && make install &&cd ..ln -s /application/mysql-5.5.49/ /application/mysql

• 创建多实例目录

mkdir /data/{3306,3307}/data -p

• 3306的my.cnf

vim /data/3306/my.cnf [client]port=3306socket= /data/3306/mysql.sock[mysql]no-auto-rehash[mysqld]user    = mysqlport    = 3306socket  = /data/3306/mysql.sockbasedir = /application/mysqldatadir = /data/3306/dataopen_files_limit=1024back_log = 600max_connections = 800max_connect_errors = 3000table_cache = 614external-locking = FALSEmax_allowed_packet =8Msort_buffer_size = 1Mjoin_buffer_size = 1Mthread_cache_size = 100thread_concurrency = 2query_cache_size = 2Mquery_cache_limit = 1Mquery_cache_min_res_unit = 2k#default_table_type = InnoDBthread_stack = 192K#transaction_isolation = READ-COMMITTEDtmp_table_size = 2Mmax_heap_table_size = 2Mlong_query_time = 1#log_long_format#log-error = /data/3306/error.log#log-slow-queries =/data/3306/slow.logpid-file = /data/3306/mysql.pidlog-bin = /data/3306/mysql-binrelay-log = /data/3306/relay-binrelay-log-info-file = /data/3306/relay-log.infobinlog_cache_size = 1Mmax_binlog_cache_size = 1Mmax_binlog_size = 2Mexpire_logs_days = 7key_buffer_size = 16Mread_buffer_size = 1Mread_rnd_buffer_size = 1Mbulk_insert_buffer_size = 1M#myisam_sort_buffer_size = 1M#myisam_max_sort_file_size = 10G#myisam_max_extra_sort_file_size = 10G#myisam_repair_threads = 1#myisam_recoverlower_case_table_names = 1skip-name-resolveslave-skip-errors = 1032,1062replicate-ignore-db=mysqlserver-id = 1innodb_additional_mem_pool_size = 4Minnodb_buffer_pool_size = 32Minnodb_data_file_path = ibdata1:128M:autoextendinnodb_file_io_threads = 4innodb_thread_concurrency = 8innodb_flush_log_at_trx_commit = 2innodb_log_buffer_size = 2Minnodb_log_file_size = 4Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 90innodb_lock_wait_timeout = 120innodb_file_per_table = 0[mysqldump]quickmax_allowed_packet = 2M[mysqld_safe]log-error=/data/3306/mysql3306.errpid-file=/data/3306/mysqld.pid

• 3307的my.cnf

vim /data/3307/my.cnf[client]port=3307socket= /data/3307/mysql.sock[mysql]no-auto-rehash[mysqld]user    = mysqlport    = 3307socket  = /data/3307/mysql.sockbasedir = /application/mysqldatadir = /data/3307/dataopen_files_limit=1024back_log = 600max_connections = 800max_connect_errors = 3000table_cache = 614external-locking = FALSEmax_allowed_packet =8Msort_buffer_size = 1Mjoin_buffer_size = 1Mthread_cache_size = 100thread_concurrency = 2query_cache_size = 2Mquery_cache_limit = 1Mquery_cache_min_res_unit = 2k#default_table_type = InnoDBthread_stack = 192K#transaction_isolation = READ-COMMITTEDtmp_table_size = 2Mmax_heap_table_size = 2M#long_query_time = 1#log_long_format#log-error = /data/3307/error.log#log-slow-queries =/data/3307/slow.logpid-file = /data/3307/mysql.pid#log-bin = /data/3307/mysql-binrelay-log = /data/3307/relay-binrelay-log-info-file = /data/3306/relay-log.infobinlog_cache_size = 1Mmax_binlog_cache_size = 1Mmax_binlog_size = 2Mexpire_logs_days = 7key_buffer_size = 16Mread_buffer_size = 1Mread_rnd_buffer_size = 1Mbulk_insert_buffer_size = 1M#myisam_sort_buffer_size = 1M#myisam_max_sort_file_size = 10G#myisam_max_extra_sort_file_size = 10G#myisam_repair_threads = 1#myisam_recoverlower_case_table_names = 1skip-name-resolveslave-skip-errors = 1032,1062replicate-ignore-db=mysqlserver-id = 3innodb_additional_mem_pool_size = 4Minnodb_buffer_pool_size = 32Minnodb_data_file_path = ibdata1:128M:autoextendinnodb_file_io_threads = 4innodb_thread_concurrency = 8innodb_flush_log_at_trx_commit = 2innodb_log_buffer_size = 2Minnodb_log_file_size = 4Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 90innodb_lock_wait_timeout = 120innodb_file_per_table = 0[mysqldump]quickmax_allowed_packet = 2M[mysqld_safe]log-error=/data/3307/mysql3307.errpid-file=/data/3307/mysqld.pid

• 多实例启动脚本3306

vim /data/3306/mysql#!/bin/sh#############################################################################initport=3306mysql_user="root"mysql_pwd="123456"CmdPath="/application/mysql/bin"mysql_sock="/data/${port}/mysql.sock"#startup functionfunction_start_mysql(){ if [ ! -e "$mysql_sock" ];then  printf "Starting MySQL...\n"  /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null & else  printf "MySQL is running...\n" exit fi}#stop functionfunction_stop_mysql(){      if [ ! -e "$mysql_sock" ];then        printf "MySQL is stopped...\n"        exit      else        printf "Stoping MySQL...\n"        ${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown      fi}#restart functionfunction_restart_mysql(){  printf "Restarting MySQL...\n"  function_stop_mysql  sleep 2  function_start_mysql}case $1 in start) function_start_mysql;;stop) function_stop_mysql;;restart) function_restart_mysql;;*)  printf "Usage: /data/${port}/mysql {start|stop|restart}\n"esac

• 3307启动脚本

\cp /data/3306/mysql /data/3307/mysqlsed -i 's#3306#3307#g' /data/3307/mysql

• 初始化数据库，增加权限，启动多实例脚本

cd /application/mysql/scripts./mysql_install_db --basedir=/application/mysql --datadir=/data/3306/data --user=mysql./mysql_install_db --basedir=/application/mysql --datadir=/data/3307/data --user=mysql find /data -type f -name "mysql"|xargs chmod +x mysqlchown -R mysql.mysql /data                       /data/3306/mysql start/data/3307/mysql startss -lntup|grep mysqld

• 修改环境变量

\cp /application/mysql/bin/* /usr/local/sbinmysql -S /data/3306/mysql.sockmysql -S /data/3307/mysql.sock

删除database test，多余用户

show databases;drop database test;select  user,host from mysql.user;drop user ""@"mysql01";drop user ""@"localhost";drop user "root"@"::1";drop user "root"@"mysql01";

• 修改mysql密码

mysqladmin -uroot password '123456' -S /data/3306/mysql.sockmysql -uroot -p123456 -S /data/3306/mysql.sock

• 增加开机自启动

echo "/data/3306/mysql start" >>/etc/rc.localecho "/data/3307/mysql start" >>/etc/rc.local 

---

• cmake解释

-DCMAKE_INSTALL_PREFIX= 指向mysql安装目录-DINSTALL_SBINDIR=sbin 指向可执行文件目录（prefix/sbin）-DMYSQL_DATADIR=/var/lib/mysql 指向mysql数据文件目录（/var/lib/mysql）-DSYSCONFDIR=/etc/mysql 指向mysql配置文件目录（/etc/mysql）-DINSTALL_PLUGINDIR=lib/mysql/plugin 指向插件目录（prefix/lib/mysql/plugin）-DINSTALL_MANDIR=share/man 指向man文档目录（prefix/share/man）-DINSTALL_SHAREDIR=share 指向aclocal/mysql.m4安装目录（prefix/share）-DINSTALL_LIBDIR=lib/mysql 指向对象代码库目录（prefix/lib/mysql）-DINSTALL_INCLUDEDIR=include/mysql 指向头文件目录（prefix/include/mysql）-DINSTALL_INFODIR=share/info 指向info文档存放目录（prefix/share/info）prefix官方推荐设为/usrStorage Engine相关类型csv,myisam,myisammrg,heap,innobase,archive,blackhole若想启用某个引擎的支持：-DWITH_<ENGINE>_STORAGE_ENGINE=1如：-DWITH_INNOBASE_STORAGE_ENGINE=1-DWITH_ARCHIVE_STORAGE_ENGINE=1-DWITH_BLACKHOLE_STORAGE_ENGINE=1若想禁用某个引擎的支持：-DWITHOUT_<ENGINE>_STORAGE_ENGINE=1如：-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1-DWITHOUT_FEDERATED_STORAGE_ENGINE=1-DWITHOUT_PARTITION_STORAGE_ENGINE=1Library相关-DWITH_READLINE=1 启用readline库支持（提供可编辑的命令行）-DWITH_SSL=system 启用ssl库支持（安全套接层）-DWITH_ZLIB=system 启用libz库支持（zib、gzib相关）-DWTIH_LIBWRAP=0 禁用libwrap库（实现了通用TCP包装的功能，为网络服务守护进程使用）-DMYSQL_TCP_PORT=3306 指定TCP端口为3306-DMYSQL_UNIX_ADDR=/tmp/mysqld.sock 指定mysql.sock路径-DENABLED_LOCAL_INFILE=1 启用本地数据导入支持-DEXTRA_CHARSETS=all 启用额外的字符集类型（默认为all）-DDEFAULT_CHARSET=utf8 指定默认的字符集为utf8-DDEFAULT_COLLATION=utf8_general_ci 设定默认排序规则（utf8_general_ci快速/utf8_unicode_ci准确）-DWITH_EMBEDDED_SERVER=1 编译嵌入式服务器支持-DMYSQL_USER=mysql 指定mysql用户(默认为mysql)-DWITH_DEBUG=0 禁用debug（默认为禁用）-DENABLE_PROFILING=0 禁用Profiling分析（默认为开启）-DWITH_COMMENT='string' 一个关于编译环境的描述性注释