操作系统：CentOS release 6.7 (Final)
内核版本：2.6.32-573.el6.x86_64
mysql版本号：mysql-5.5.32
1）安装mysql所需的依赖包
# adduser mysql -s /sbin/nologin -M# mkdir -p /data/{3306,3307}/data# tree /data//data/├── 3306│?? └── data└── 3307 └── data4 directories, 0 files# yum -y install ncurse-devel libaio-devel 2）安装编译mysql需要的软件
# tar xf cmake-2.8.8.tar.gz # cd cmake-2.8.8# ./configure# gmake# which cmake/usr/bin/cmake
3）采用编译安装mysql
# ls -l |grep mysql-rw-r--r-- 1 root root 186722932 6月 27 05:03 mysql-5.5.32-linux2.6-x86_64.tar.gz-rw-r--r-- 1 root root 24596474 6月 27 05:10 mysql-5.5.32.tar.gz说明：mysql-5.5.32-linux2.6-x86_64.tar.gz为二进制安装包、mysql-5.5.32.tar.gz为源码安装包# tar xf mysql-5.5.32.tar.gz # cd mysql-5.5.32# cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.32 \-DMYSQL_DATADIR=/application/mysql-5.5.32/data \-DMYSQL_UNIX_ADDR=/application/mysql-5.5.32/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=0# make# make install
4）创建mysql多实例的数据文件目录
# mkdir -p /data/{3306,3307}/data# tree /data# ln -s /application/mysql-5.5.32/ /application/mysql# ll /application/mysqllrwxrwxrwx 1 root root 26 10月 8 22:50 /application/mysql -> /application/mysql-5.5.32/# cd /data/3306# ls -lrt总用量 12drwxr-xr-x 2 root root 4096 10月 8 21:13 data-rw-r--r-- 1 root root 1899 10月 29 2013 my.cnf-rw-r--r-- 1 root root 1307 7月 15 2013 mysql 5）编辑mysql实例配置文件
a、创建3306实例配置文件
# 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/mysql_oldboy3306.errpid-file=/data/3306/mysqld.pid
b、创建3306实例启动脚本
# vim /data/3306/mysql #!/bin/sh#initport=3306mysql_user="root"mysql_pwd="redhat12345"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 instart) function_start_mysql;;stop) function_stop_mysql;;restart) function_restart_mysql;;*) printf "Usage: /data/${port}/mysql {start|stop|restart}\n"esac c、创建3307实例配置文件
# 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/3307/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/mysql_oldboy3307.errpid-file=/data/3307/mysqld.pid
d、创建3307实例启动脚本
#!/bin/sh#initport=3307mysql_user="root"mysql_pwd="redhat12345"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 instart) function_start_mysql;;stop) function_stop_mysql;;restart) function_restart_mysql;;*) printf "Usage: /data/${port}/mysql {start|stop|restart}\n"esac 6）配置mysql多实例的文件权限
# chown -R mysql.mysql /data# find /data -name mysql|xargs ls -l-rw-r--r-- 1 mysql mysql 1312 10月 8 23:03 /data/3306/mysql-rw-r--r-- 1 mysql mysql 1312 10月 8 23:03 /data/3307/mysql# find /data -name mysql|xargs chmod 700# find /data -name mysql|xargs ls -l-rwx------ 1 mysql mysql 1312 10月 8 23:03 /data/3306/mysql-rwx------ 1 mysql mysql 1312 10月 8 23:03 /data/3307/mysql
7）将mysql相关命令加入全局路径
# ls /application/mysql/bin/mysql/application/mysql/bin/mysql# echo 'export PATH=/application/mysql/bin:$PATH'>>/etc/profile# tail -1 /etc/profileexport PATH=/application/mysql/bin:$PATH# source /etc/profile# echo $PATH/application/mysql/bin:/application/mysql/bin/:/application/nginx/sbin/:/application/mysql/bin/:/application/nginx/sbin/:/usr/lib64/qt-3.3/bin:/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
8）初始化实例的数据库文件
# ./mysql_install_db --basedir=/application/mysql --datadir=/data/3306/data --user=mysql# ./mysql_install_db --basedir=/application/mysql --datadir=/data/3307/data --user=mysql# echo $?0# 启动多实例# /data/3306/mysql startStarting MySQL...# /data/3307/mysql startStarting MySQL...# 查看数据库# netstat -tunlp | grep 330*tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 28533/mysqld tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 29250/mysqld # 配置mysql多实例数据库开机自启动# echo "# mysql multi instances startup">>/etc/rc.local# echo "rm -rf /data/3306/mysql.sock">>/etc/rc.local# echo "rm -rf /data/3307/mysql.sock">>/etc/rc.local# echo "/data/3306/mysql start">>/etc/rc.local # echo "/data/3307/mysql start">>/etc/rc.local # 登录mysql进行测试：# mysql -S /data/3306/mysql.sock # mysql -S /data/3307/mysql.sock
9) mysql安全设置
# mysqladmin -u root -S /data/3306/mysql.sock password 'redhat12345'# mysqladmin -u root -S /data/3307/mysql.sock password 'redhat12345'
10) 登录进入mysql
# mysql -uroot -S /data/3306/mysql.sock -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 4Server version: 5.5.32-log Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || test |+--------------------+4 rows in set (0.00 sec)mysql> select user();+----------------+| user() |+----------------+| root@localhost |+----------------+1 row in set (0.00 sec)# find /data -type f -name "mysql" -exec chown root.root {} \;# find /data -type f -name "mysql" -exec chmod 700 {} \;# find /data -type f -name "mysql" -exec ls -l {} \;-rwx------ 1 root root 1312 10月 8 23:03 /data/3306/mysql-rwx------ 1 root root 1312 10月 8 23:03 /data/3307/mysql 11) mysql的启停测试
# /data/3306/mysql stopStoping MySQL...# netstat -tunlp | grep 330*tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 29250/mysqld # /data/3306/mysql startStarting MySQL... # netstat -tunlp | grep 330*tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 30168/mysqld tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 29250/mysqld
12) 在远程主机进行测试
a、服务器端创建数据库并添加授权账号
mysql> create database wanlong;Query OK, 1 row affected (0.00 sec)mysql> create user wan identified by "redhat";Query OK, 0 rows affected (0.00 sec)mysql> grant all privileges on wanlong.* to 'wan'@'10.10.10.%' identified by 'redhat' with grant option;Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || test || wanlong |+--------------------+5 rows in set (0.00 sec)
b、客户端进行访问：
# mysql -uwan -predhat -h 10.10.10.129-u：用户名-p：密码-h：远程主机mysql> select user();+------------------+| user() |+------------------+| wan@10.10.10.130 |+------------------+1 row in set (0.00 sec)mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || test || wanlong |+--------------------+3 rows in set (0.01 sec)