操作系统:CentOS release 6.7 (Final)
内核版本:2.6.32-573.el6.x86_64
mysql版本号:mysql-5.5.32
1)安装mysql所需的依赖包
<code># adduser mysql -s /sbin/nologin -M</code>
<code># mkdir -p /data/{3306,3307}/data</code>
<code># tree /data/</code>
<code>/data/</code>
<code>├── 3306</code>
<code>│?? └── data</code>
<code>└── 3307</code>
<code> </code><code>└── data</code>
<code>4 directories, 0 files</code>
<code># yum -y install ncurse-devel libaio-devel</code>
2)安装编译mysql需要的软件
<code># tar xf cmake-2.8.8.tar.gz </code>
<code># cd cmake-2.8.8</code>
<code># ./configure</code>
<code># gmake</code>
<code># which cmake</code>
<code>/usr/bin/cmake</code>
3)采用编译安装mysql
<code># ls -l |grep mysql</code>
<code>-rw-r--r-- 1 root root 186722932 6月 27 05:03 mysql-5.5.32-linux2.6-x86_64.</code><code>tar</code><code>.gz</code>
<code>-rw-r--r-- 1 root root 24596474 6月 27 05:10 mysql-5.5.32.</code><code>tar</code><code>.gz</code>
<code>说明:mysql-5.5.32-linux2.6-x86_64.</code><code>tar</code><code>.gz为二进制安装包、mysql-5.5.32.</code><code>tar</code><code>.gz为源码安装包</code>
<code># tar xf mysql-5.5.32.tar.gz </code>
<code># cd mysql-5.5.32</code>
<code># cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.32 \</code>
<code>-DMYSQL_DATADIR=</code><code>/application/mysql-5</code><code>.5.32</code><code>/data</code> <code>\</code>
<code>-DMYSQL_UNIX_ADDR=</code><code>/application/mysql-5</code><code>.5.32</code><code>/tmp/mysql</code><code>.sock \</code>
<code>-DDEFAULT_CHARSET=utf8 \</code>
<code>-DDEFAULT_COLLATION=utf8_general_ci \</code>
<code>-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \</code>
<code>-DENABLED_LOCAL_INFILE=ON \</code>
<code>-DWITH_INNOBASE_STORAGE_ENGINE=1 \</code>
<code>-DWITH_FEDERATED_STORAGE_ENGINE=1 \</code>
<code>-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \</code>
<code>-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \</code>
<code>-DWITHOUT_PARTITION_STORAGE_ENGINE=1 \</code>
<code>-DWITH_FAST_MUTEXES=1 \</code>
<code>-DWITH_ZLIB=bundled \</code>
<code>-DENABLED_LOCAL_INFILE=1 \</code>
<code>-DWITH_READLINE=1 \</code>
<code>-DWITH_EMBEDDED_SERVER=1 \</code>
<code>-DWITH_DEBUG=0</code>
<code># make</code>
<code># make install</code>
4)创建mysql多实例的数据文件目录
<code># tree /data</code>
<code># ln -s /application/mysql-5.5.32/ /application/mysql</code>
<code># ll /application/mysql</code>
<code>lrwxrwxrwx 1 root root 26 10月 8 22:50 </code><code>/application/mysql</code> <code>-> </code><code>/application/mysql-5</code><code>.5.32/</code>
<code># cd /data/3306</code>
<code># ls -lrt</code>
<code>总用量 12</code>
<code>drwxr-xr-x 2 root root 4096 10月 8 21:13 data</code>
<code>-rw-r--r-- 1 root root 1899 10月 29 2013 my.cnf</code>
<code>-rw-r--r-- 1 root root 1307 7月 15 2013 mysql</code>
5)编辑mysql实例配置文件
a、创建3306实例配置文件
<code># vim /data/3306/my.cnf </code>
<code>[client]</code>
<code>port = 3306</code>
<code>socket = </code><code>/data/3306/mysql</code><code>.sock</code>
<code>[mysql]</code>
<code>no-auto-rehash</code>
<code>[mysqld]</code>
<code>user = mysql</code>
<code>port = 3306</code>
<code>socket = </code><code>/data/3306/mysql</code><code>.sock</code>
<code>basedir = </code><code>/application/mysql</code>
<code>datadir = </code><code>/data/3306/data</code>
<code>open_files_limit = 1024</code>
<code>back_log = 600</code>
<code>max_connections = 800</code>
<code>max_connect_errors = 3000</code>
<code>table_cache = 614</code>
<code>external-locking = FALSE</code>
<code>max_allowed_packet =8M</code>
<code>sort_buffer_size = 1M</code>
<code>join_buffer_size = 1M</code>
<code>thread_cache_size = 100</code>
<code>thread_concurrency = 2</code>
<code>query_cache_size = 2M</code>
<code>query_cache_limit = 1M</code>
<code>query_cache_min_res_unit = 2k</code>
<code>#default_table_type = InnoDB</code>
<code>thread_stack = 192K</code>
<code>#transaction_isolation = READ-COMMITTED</code>
<code>tmp_table_size = 2M</code>
<code>max_heap_table_size = 2M</code>
<code>long_query_time = 1</code>
<code>#log_long_format</code>
<code>#log-error = /data/3306/error.log</code>
<code>#log-slow-queries = /data/3306/slow.log</code>
<code>pid-</code><code>file</code> <code>= </code><code>/data/3306/mysql</code><code>.pid</code>
<code>log-bin = </code><code>/data/3306/mysql-bin</code>
<code>relay-log = </code><code>/data/3306/relay-bin</code>
<code>relay-log-info-</code><code>file</code> <code>= </code><code>/data/3306/relay-log</code><code>.info</code>
<code>binlog_cache_size = 1M</code>
<code>max_binlog_cache_size = 1M</code>
<code>max_binlog_size = 2M</code>
<code>expire_logs_days = 7</code>
<code>key_buffer_size = 16M</code>
<code>read_buffer_size = 1M</code>
<code>read_rnd_buffer_size = 1M</code>
<code>bulk_insert_buffer_size = 1M</code>
<code>#myisam_sort_buffer_size = 1M</code>
<code>#myisam_max_sort_file_size = 10G</code>
<code>#myisam_max_extra_sort_file_size = 10G</code>
<code>#myisam_repair_threads = 1</code>
<code>#myisam_recover</code>
<code>lower_case_table_names = 1</code>
<code>skip-name-resolve</code>
<code>slave-skip-errors = 1032,1062</code>
<code>replicate-ignore-db=mysql</code>
<code>server-</code><code>id</code> <code>= 1</code>
<code>innodb_additional_mem_pool_size = 4M</code>
<code>innodb_buffer_pool_size = 32M</code>
<code>innodb_data_file_path = ibdata1:128M:autoextend</code>
<code>innodb_file_io_threads = 4</code>
<code>innodb_thread_concurrency = 8</code>
<code>innodb_flush_log_at_trx_commit = 2</code>
<code>innodb_log_buffer_size = 2M</code>
<code>innodb_log_file_size = 4M</code>
<code>innodb_log_files_in_group = 3</code>
<code>innodb_max_dirty_pages_pct = 90</code>
<code>innodb_lock_wait_timeout = 120</code>
<code>innodb_file_per_table = 0</code>
<code>[mysqldump]</code>
<code>quick</code>
<code>max_allowed_packet = 2M</code>
<code>[mysqld_safe]</code>
<code>log-error=</code><code>/data/3306/mysql_oldboy3306</code><code>.err</code>
<code>pid-</code><code>file</code><code>=</code><code>/data/3306/mysqld</code><code>.pid</code>
b、创建3306实例启动脚本
<code># vim /data/3306/mysql </code>
<code>#!/bin/sh</code>
<code>#init</code>
<code>port=3306</code>
<code>mysql_user=</code><code>"root"</code>
<code>mysql_pwd=</code><code>"redhat12345"</code>
<code>CmdPath=</code><code>"/application/mysql/bin"</code>
<code>mysql_sock=</code><code>"/data/${port}/mysql.sock"</code>
<code>#startup function</code>
<code>function_start_mysql()</code>
<code>{</code>
<code> </code><code>if</code> <code>[ ! -e </code><code>"$mysql_sock"</code> <code>];</code><code>then</code>
<code> </code><code>printf</code> <code>"Starting MySQL...\n"</code>
<code> </code><code>/bin/sh</code> <code>${CmdPath}</code><code>/mysqld_safe</code> <code>--defaults-</code><code>file</code><code>=</code><code>/data/</code><code>${port}</code><code>/my</code><code>.cnf 2>&1 > </code><code>/dev/null</code> <code>&</code>
<code> </code><code>else</code>
<code> </code><code>printf</code> <code>"MySQL is running...\n"</code>
<code> </code><code>exit</code>
<code> </code><code>fi</code>
<code>}</code>
<code>#stop function</code>
<code>function_stop_mysql()</code>
<code> </code><code>printf</code> <code>"MySQL is stopped...\n"</code>
<code> </code><code>exit</code>
<code> </code><code>printf</code> <code>"Stoping MySQL...\n"</code>
<code>${CmdPath}</code><code>/mysqladmin</code> <code>-u ${mysql_user} -p${mysql_pwd} -S </code><code>/data/</code><code>${port}</code><code>/mysql</code><code>.sock </code>
<code>shutdown</code>
<code> </code><code>fi</code>
<code>#restart function</code>
<code>function_restart_mysql()</code>
<code> </code><code>printf</code> <code>"Restarting MySQL...\n"</code>
<code> </code><code>function_stop_mysql</code>
<code> </code><code>sleep</code> <code>2</code>
<code> </code><code>function_start_mysql</code>
<code>case</code> <code>$1 </code><code>in</code>
<code>start)</code>
<code>;;</code>
<code>stop)</code>
<code>restart)</code>
<code> </code><code>function_restart_mysql</code>
<code>*)</code>
<code> </code><code>printf</code> <code>"Usage: /data/${port}/mysql {start|stop|restart}\n"</code>
<code>esac</code>
c、创建3307实例配置文件
<code># vim /data/3307/my.cnf </code>
<code>port = 3307</code>
<code>socket = </code><code>/data/3307/mysql</code><code>.sock</code>
<code>port = 3307</code>
<code>socket = </code><code>/data/3307/mysql</code><code>.sock</code>
<code>datadir = </code><code>/data/3307/data</code>
<code>#long_query_time = 1</code>
<code>#log-error = /data/3307/error.log</code>
<code>#log-slow-queries = /data/3307/slow.log</code>
<code>pid-</code><code>file</code> <code>= </code><code>/data/3307/mysql</code><code>.pid</code>
<code>#log-bin = /data/3307/mysql-bin</code>
<code>relay-log = </code><code>/data/3307/relay-bin</code>
<code>relay-log-info-</code><code>file</code> <code>= </code><code>/data/3307/relay-log</code><code>.info</code>
<code>server-</code><code>id</code> <code>= 3</code>
<code>log-error=</code><code>/data/3307/mysql_oldboy3307</code><code>.err</code>
<code>pid-</code><code>file</code><code>=</code><code>/data/3307/mysqld</code><code>.pid</code>
d、创建3307实例启动脚本
<code>port=3307</code>
<code> </code><code>${CmdPath}</code><code>/mysqladmin</code> <code>-u ${mysql_user} -p${mysql_pwd} -S </code><code>/data/</code><code>${port}</code><code>/mysql</code><code>.sock </code><code>shutdown</code>
6)配置mysql多实例的文件权限
<code># chown -R mysql.mysql /data</code>
<code># find /data -name mysql|xargs ls -l</code>
<code>-rw-r--r-- 1 mysql mysql 1312 10月 8 23:03 </code><code>/data/3306/mysql</code>
<code>-rw-r--r-- 1 mysql mysql 1312 10月 8 23:03 </code><code>/data/3307/mysql</code>
<code># find /data -name mysql|xargs chmod 700</code>
<code>-rwx------ 1 mysql mysql 1312 10月 8 23:03 </code><code>/data/3306/mysql</code>
<code>-rwx------ 1 mysql mysql 1312 10月 8 23:03 </code><code>/data/3307/mysql</code>
7)将mysql相关命令加入全局路径
<code># ls /application/mysql/bin/mysql</code>
<code>/application/mysql/bin/mysql</code>
<code># echo 'export PATH=/application/mysql/bin:$PATH'>>/etc/profile</code>
<code># tail -1 /etc/profile</code>
<code>export</code> <code>PATH=</code><code>/application/mysql/bin</code><code>:$PATH</code>
<code># source /etc/profile</code>
<code># echo $PATH</code>
<code>/application/mysql/bin</code><code>:</code><code>/application/mysql/bin/</code><code>:</code><code>/application/nginx/sbin/</code><code>:</code><code>/application/mysql/bin/</code><code>:</code><code>/application/nginx/sbin/</code><code>:</code><code>/usr/lib64/qt-3</code><code>.3</code><code>/bin</code><code>:</code><code>/usr/kerberos/sbin</code><code>:</code><code>/usr/kerberos/bin</code><code>:</code><code>/usr/local/sbin</code><code>:</code><code>/usr/local/bin</code><code>:</code><code>/sbin</code><code>:</code><code>/bin</code><code>:</code><code>/usr/sbin</code><code>:</code><code>/usr/bin</code><code>:</code><code>/root/bin</code>
8)初始化实例的数据库文件
<code># ./mysql_install_db --basedir=/application/mysql --datadir=/data/3306/data --user=mysql</code>
<code># ./mysql_install_db --basedir=/application/mysql --datadir=/data/3307/data --user=mysql</code>
<code># echo $?</code>
<code>0</code>
<code># 启动多实例</code>
<code># /data/3306/mysql start</code>
<code>Starting MySQL...</code>
<code># /data/3307/mysql start</code>
<code># 查看数据库</code>
<code># netstat -tunlp | grep 330*</code>
<code>tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 28533</code><code>/mysqld</code>
<code>tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 29250</code><code>/mysqld</code>
<code># 配置mysql多实例数据库开机自启动</code>
<code># echo "# mysql multi instances startup">>/etc/rc.local</code>
<code># echo "rm -rf /data/3306/mysql.sock">>/etc/rc.local</code>
<code># echo "rm -rf /data/3307/mysql.sock">>/etc/rc.local</code>
<code># echo "/data/3306/mysql start">>/etc/rc.local </code>
<code># echo "/data/3307/mysql start">>/etc/rc.local </code>
<code># 登录mysql进行测试:</code>
<code># mysql -S /data/3306/mysql.sock </code>
<code># mysql -S /data/3307/mysql.sock</code>
9) mysql安全设置
<code># mysqladmin -u root -S /data/3306/mysql.sock password 'redhat12345'</code>
<code># mysqladmin -u root -S /data/3307/mysql.sock password 'redhat12345'</code>
10) 登录进入mysql
<code># mysql -uroot -S /data/3306/mysql.sock -p</code>
<code>Enter password: </code>
<code>Welcome to the MySQL monitor. Commands end with ; or \g.</code>
<code>Your MySQL connection </code><code>id</code> <code>is 4</code>
<code>Server version: 5.5.32-log Source distribution</code>
<code>Copyright (c) 2000, 2013, Oracle and</code><code>/or</code> <code>its affiliates. All rights reserved.</code>
<code>Oracle is a registered trademark of Oracle Corporation and</code><code>/or</code> <code>its</code>
<code>affiliates. Other names may be trademarks of their respective</code>
<code>owners.</code>
<code>Type </code><code>'help;'</code> <code>or </code><code>'\h'</code> <code>for</code> <code>help. Type </code><code>'\c'</code> <code>to </code><code>clear</code> <code>the current input statement.</code>
<code>mysql> show databases;</code>
<code>+--------------------+</code>
<code>| Database |</code>
<code>| information_schema |</code>
<code>| mysql |</code>
<code>| performance_schema |</code>
<code>| </code><code>test</code> <code>|</code>
<code>4 rows </code><code>in</code> <code>set</code> <code>(0.00 sec)</code>
<code>mysql> </code><code>select</code> <code>user();</code>
<code>+----------------+</code>
<code>| user() |</code>
<code>| root@localhost |</code>
<code>1 row </code><code>in</code> <code>set</code> <code>(0.00 sec)</code>
<code># find /data -type f -name "mysql" -exec chown root.root {} \;</code>
<code># find /data -type f -name "mysql" -exec chmod 700 {} \;</code>
<code># find /data -type f -name "mysql" -exec ls -l {} \;</code>
<code>-rwx------ 1 root root 1312 10月 8 23:03 </code><code>/data/3306/mysql</code>
<code>-rwx------ 1 root root 1312 10月 8 23:03 </code><code>/data/3307/mysql</code>
11) mysql的启停测试
<code># /data/3306/mysql stop</code>
<code>Stoping MySQL...</code>
<code>tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 29250</code><code>/mysqld</code>
<code>Starting MySQL... </code>
<code>tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 30168</code><code>/mysqld</code>
<code>tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 29250</code><code>/mysqld</code>
12) 在远程主机进行测试
a、服务器端创建数据库并添加授权账号
<code>mysql> create database wanlong;</code>
<code>Query OK, 1 row affected (0.00 sec)</code>
<code>mysql> create user wan identified by </code><code>"redhat"</code><code>;</code>
<code>Query OK, 0 rows affected (0.00 sec)</code>
<code>mysql> grant all privileges on wanlong.* to </code><code>'wan'</code><code>@</code><code>'10.10.10.%'</code> <code>identified by </code><code>'redhat'</code> <code>with grant option;</code>
<code>mysql> flush privileges;</code>
<code>| wanlong |</code>
<code>5 rows </code><code>in</code> <code>set</code> <code>(0.00 sec)</code>
b、客户端进行访问:
<code># mysql -uwan -predhat -h 10.10.10.129</code>
<code>-u:用户名</code>
<code>-p:密码</code>
<code>-h:远程主机</code>
<code>mysql> </code><code>select</code> <code>user();</code>
<code>+------------------+</code>
<code>| user() |</code>
<code>| [email protected] |</code>
<code>3 rows </code><code>in</code> <code>set</code> <code>(0.01 sec)</code>
<code></code>
本文转自 冰冻vs西瓜 51CTO博客,原文链接:http://blog.51cto.com/molewan/1860198,如需转载请自行联系原作者