MySQL 5.5.35 单机多实例配置详解

转载

mob604756fea1c5 2016-12-13 21:18:00

文章标签 mysql 多实例 oracle 配置文件 d3 文章分类 代码人生

一、前言

二、概述

三、环境准备

四、安装MySQL 5.5.35

五、新建支持多实例的配置文件（我这里配置的是四个实例）

六、初始化多实例数据库

七、提供管理脚本 mysqld_multi.server

八、整体备份方便后续迁移

九、管理MySQL多实例

十、登录MySQL多实例

十一、其它管理配置

十二、总结

注，测试环境 CentOS 6.4 x86_64，软件版本 MySQL 5.5.35，软件下载地址：http://dev.mysql.com/downloads/mysql/5.1.html#downloads。

1.应用场景

采用了数据伪分布式架构的原因，而项目启动初期又不一定有那多的用户量，为此先一组物理数据库服务器，但部署多个实例，方便后续迁移；
为规避mysql对SMP架构不支持的缺陷，使用多实例绑定处理器的办法，把不同的数据库分配到不同的实例上提供数据服务；
一台物理数据库服务器支撑多个数据库的数据服务，为提高mysql复制的从机的恢复效率，采用多实例部署；
已经为双主复制的mysql数据库服务器架构，想部分重要业务的数据多一份异地机房的热备份，而mysql复制暂不支持多主的复制模式，且不给用户提供服务，为有效控制成本，会考虑异地机房部署一台性能超好的物理服务器，甚至外加磁盘柜的方式，为此也会部署多实例；
传统游戏行业的MMO/MMORPG，以及Web Game，每一个服都对应一个数据库，而可能要做很多数据查询和数据订正的工作，为减少维护而出错的概率，也可能采用多实例部署的方式，按区的概念分配数据库；

上面的应用场景介绍主要参考这篇文章：http://www.zhdba.com/mysqlops/2011/07/30/multi-mysqld/，我们这里应用主要是基于前面三种场景。下面我们来说一下要注意的问题……

2.背景/需求、注意事项

(1).背景与需求

将所有的安装文件、配置文件、数据目录全部放存/data/mysql目录中，便于今后实现快速迁移、整体备份和快速复制；
在一台服务器上运行四个MySQL实例，分别绑定在3306、3307、3308、3309端口上；
四个实例都开启binlog日志，数据目录分别存放在/data/mysql/data、/data/mysql/data2、/data/mysql/data3、/data/mysql/data4
四个实例均采用InnoDB作为默认的存储引擎，字符编码采用UTF-8；
四个实例均采用相同的性能优化配置参数；

(2).注意事项

在编译安装时，将数据库的配置文件my.cnf以及data目录等均指向到/data/mysql目录中；
通过mysqld_multi的方式来管理四个不同的实例，采用相同的配置文件共享性能优化配置参数；
在同一个配置文件中，利用[mysqld1]、[mysqld2]、[mysqld3]、[mysqld4]标签实现不同实例的差异化配置；

三、环境准备

1.安装yum源

12	`[root@node1 src]``# wget http://mirrors.hustunique.com/epel/6/x86_64/epel-release-6-8.noarch.rpm``[root@node1 src]``# rpm -ivh epel-release-6-8.noarch.rpm`

2.同步时间

123	`[root@node1 src]``# yum install -y ntp``[root@node1 src]``# ntpdate 202.120.2.101``[root@node1 src]``# hwclock –w`

3.安装mysql5.5依赖包

1	`[root@node1 ~]``# yum install -y autoconf* automake* zlib* libxml* ncurses-devel* libgcrypt* libtool* openssl*`

4.安装cmake

1	`[root@node1 ~]``# yum install -y cmake`

四、安装MySQL 5.5.35

12	`[root@node1 ~]``# mkdir /data/mysql``[root@node1 ~]``# mkdir /data/mysql/data`

2.创建mysql用户与组

123	`[root@node1 ~]``# useradd mysql``[root@node1 ~]``# id mysql` `uid=500(mysql) gid=500(mysql) 组=500(mysql)`

12	`[root@node1 ~]``# chown -R mysql.mysql /data/mysql/` `[root@node1 ~]``# chown -R mysql.mysql /data/mysql/data`

4.安装mysql

12345	`[root@node1 ~]``# cd src/` `[root@node1 src]``# tar xf mysql-5.5.35.tar.gz` `[root@node1 src]``# cd mysql-5.5.35``[root@node1 mysql-5.5.35]``# cmake -DCMAKE_INSTALL_PREFIX=/data/mysql -DSYSCONFDIR=/data/mysql/etc -DMYSQL_DATADIR=/data/mysql/data -DMYSQL_TCP_PORT=3306 -DMYSQL_UNIX_ADDR=/tmp/mysqld.sock -DMYSQL_USER=mysql -DEXTRA_CHARSETS=all -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_EMBEDDED_SERVER=1 -DENABLED_LOCAL_INFILE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1``[root@node1 mysql-5.5.35]``# make && make install`

好了，到这里我们的mysql就安装完成了，下面我们为mysql提供多实例配置文件。

五、新建支持多实例的配置文件（我这里配置的是四个实例）

1.删除默认的数据目录

12	`[root@node1 ~]``# cd /data/mysql/` `[root@node1 mysql]``# rm -rf data`

2.创建多实例配置需要的目录

12	`[root@node1 mysql]``# mkdir etc tmp run log binlogs data data2 data3 data4``[root@node1 mysql]``# chown -R mysql.mysql tmp run log binlogs data data2 data3 data4`

3.提供配置文件

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175	`[root@node1 ~]``# cd src/` `[root@node1 src]``# cd mysql-5.5.35``[root@node1 mysql-5.5.35]``# cp support-files/my-small.cnf /data/mysql/etc/my.cnf``[root@node1 ~]``# cd /data/mysql/etc/` `[root@node1 etc]``# vim my.cnf` `# This server may run 4+ separate instances. So we use mysqld_multi to manage their services.``[client]` `default-character-``set``= utf8``[mysqld_multi]` `mysqld =` `/data/mysql/bin/mysqld_safe` `mysqladmin =` `/data/mysql/bin/mysqladmin` `log =` `/data/mysql/log/mysqld_multi``.log` `user = root` `#password =` `# This is the general purpose database.` `# The locations are default.` `# They are left in [mysqld] in case the server is started normally instead of by mysqld_multi.``[mysqld1]` `socket =` `/data/mysql/run/mysqld``.sock` `port = 3306` `pid-``file``=` `/data/mysql/run/mysqld``.pid` `datadir =` `/data/mysql/data` `lc-messages-``dir``=` `/data/mysql/share/english` `# These support master - master replication` `#auto-increment-increment = 4` `#auto-increment-offset = 1 # Since it is master 1` `log-bin =` `/data/mysql/binlogs/bin-log-mysqld1` `log-bin-index =` `/data/mysql/binlogs/bin-log-mysqld1``.index` `#binlog-do-db = # Leave this blank if you want to control it on slave` `max_binlog_size = 1024M` `# This is exlusively for mysqld2` `# It is on 3307 with data directory /data/mysqld/data2``[mysqld2]` `socket =` `/data/mysql/run/mysqld``.sock2` `port = 3307` `pid-``file``=` `/data/mysql/run/mysqld``.pid2` `datadir =` `/data/mysql/data2` `lc-messages-``dir``=` `/data/mysql/share/english` `# Disable DNS lookups` `#skip-name-resolve` `# These support master - slave replication` `log-bin =` `/data/mysql/binlogs/bin-log-mysqld2` `log-bin-index =` `/data/mysql/binlogs/bin-log-mysqld2``.index` `#binlog-do-db = # Leave this blank if you want to control it on slave` `max_binlog_size = 1024M` `# Relay log settings``#relay-log = /data/mysql/log/relay-log-mysqld2``#relay-log-index = /data/mysql/log/relay-log-mysqld2.index``#relay-log-space-limit = 4G` `# Slow query log settings``#log-slow-queries = /data/mysql/log/slow-log-mysqld2``#long_query_time = 2``#log-queries-not-using-indexes` `# This is exlusively for mysqld3` `# It is on 3308 with data directory /data/mysqld/data3``[mysqld3]` `socket =` `/data/mysql/run/mysqld``.sock3` `port = 3308` `pid-``file``=` `/data/mysql/run/mysqld``.pid3` `datadir =` `/data/mysql/data3` `lc-messages-``dir``=` `/data/mysql/share/english``#Disable DNS lookups` `#skip-name-resolve``# These support master - slave replication` `log-bin =` `/data/mysql/binlogs/bin-log-mysqld3` `log-bin-index =` `/data/mysql/binlogs/bin-log-mysqld3``.index` `#binlog-do-db = # Leave this blank if you want to control it on slave` `max_binlog_size = 1024M` `# This is exlusively for mysqld4``# It is on 3309 with data directory /data/mysqld/data4``[mysqld4]` `socket =` `/data/mysql/run/mysqld``.sock4` `port = 3309` `pid-``file``=` `/data/mysql/run/mysqld``.pid4` `datadir =` `/data/mysql/data4` `lc-messages-``dir``=` `/data/mysql/share/english``# Disable DNS lookups` `#skip-name-resolve``# These support master - slave replication` `log-bin =` `/data/mysql/binlogs/bin-log-mysqld4` `log-bin-index =` `/data/mysql/binlogs/bin-log-mysqld4``.index` `#binlog-do-db = # Leave this blank if you want to control it on slave` `max_binlog_size = 1024M` `# The rest of the my.cnf is shared``# Here follows entries for some specific programs``# The MySQL server``[mysqld]` `basedir =` `/data/mysql` `tmpdir =` `/data/mysql/tmp` `socket =` `/data/mysql/run/mysqld``.sock` `port = 3306` `pid-``file``=` `/data/mysql/run/mysqld``.pid` `datadir =` `/data/mysql/data` `lc-messages-``dir``=` `/data/mysql/share/english` `skip-external-locking` `key_buffer_size = 16K` `max_allowed_packet = 1M` `table_open_cache = 4` `sort_buffer_size = 64K` `read_buffer_size = 256K` `read_rnd_buffer_size = 256K` `net_buffer_length = 2K` `thread_stack = 128K` `# Increase the max connections` `max_connections = 2` `# The expiration time for logs, including binlogs` `expire_logs_days = 14` `# Set the character as utf8` `character-``set``-server = utf8` `collation-server = utf8_unicode_ci` `# This is usually only needed when setting up chained replication` `#log-slave-updates` `# Enable this to make replication more resilient against server crashes and restarts` `# but can cause higher I/O on the server` `#sync_binlog = 1` `# The server id, should be unique in same network` `server-``id``= 1` `# Set this to force MySQL to use a particular engine/table-type for new tables` `# This setting can still be overridden by specifying the engine explicitly` `# in the CREATE TABLE statement` `default-storage-engine = INNODB` `# Enable Per Table Data for InnoDB to shrink ibdata1` `innodb_file_per_table = 1` `# Uncomment the following if you are using InnoDB tables` `#innodb_data_home_dir = /data/mysql/data` `#innodb_data_file_path = ibdata1:10M:autoextend` `#innodb_log_group_home_dir = /data/mysql/data` `# You can set .._buffer_pool_size up to 50 - 80 % of RAM` `# but beware of setting memory usage too high` `innodb_buffer_pool_size = 16M` `innodb_additional_mem_pool_size = 2M` `# Set .._log_file_size to 25 % of buffer pool size` `innodb_log_file_size = 5M` `innodb_log_buffer_size = 8M` `innodb_flush_log_at_trx_commit = 1` `innodb_lock_wait_timeout = 50` `[mysqldump]` `quick` `max_allowed_packet = 16M` `[mysql]` `no-auto-rehash` `[myisamchk]` `key_buffer_size = 8M` `sort_buffer_size = 8M` `[mysqlhotcopy]` `interactive-timeout` `[mysql.server]` `user = mysql` `[mysqld_safe]` `log-error =` `/data/mysql/log/mysqld``.log` `pid-``file``=` `/data/mysql/run/mysqld``.pid` `open``-files-limit = 8192`

注，MySQL自带了几个不同的配置文件，放置在/data/mysql/support-files目录下，分别是my-huge.cnf,my-innodb-heavy-4G.cnf,my-large.cnf,my-medium.cnf,my-small.cnf，通过名称我们可以很直观的了解到他们是针对不同的服务器配置的，本文的配置文件是来自于my-small.cnf的，因为我是在虚拟机上进行的设置；在生产环境中，我们可以通过参考my-huge.cnf或my-innodb-heavy-4G.cnf中的部分参数配置，来对服务器进行优化；

4.修改my.cnf读写权限

12	`[root@node1 etc]``# chown -R root.root /data/mysql/etc``[root@node1 etc]``# chmod 600 /data/mysql/etc/my.cnf`

好了，到这里我们的配置文件就设置完成了，下面我们来初始化一下数据库。

六、初始化多实例数据库

1.切换到mysql的安装目录

1	`[root@node1 ~]``# cd /data/mysql/`

2.初始化实例[mysqld1]

1	`[root@node1 mysql]``# scripts/mysql_install_db --basedir=/data/mysql --datadir=/data/mysql/data --user=mysql`

3.初始化实例[mysqld2]

1	`[root@node1 mysql]``# scripts/mysql_install_db --basedir=/data/mysql --datadir=/data/mysql/data2 --user=mysql`

4.初始化实例[mysqld3]

1	`[root@node1 mysql]``# scripts/mysql_install_db --basedir=/data/mysql --datadir=/data/mysql/data3 --user=mysql`

5.初始化实例[mysqld4]

1	`[root@node1 mysql]``# scripts/mysql_install_db --basedir=/data/mysql --datadir=/data/mysql/data4 --user=mysql`

好了，到这里我们初始化工作就完成了，下面我们来提供一下多实例的管理脚本。

七、提供管理脚本 mysqld_multi.server

1.创建管理脚本目录

1	`[root@node1 mysql]``# mkdir /data/mysql/init.d`

2.提供管理脚本

1	`[root@node1 mysql]``# cp support-files/mysqld_multi.server init.d/`

3.简单修改一下脚本

12345678910111213141516171819202122232425262728293031323334353637383940414243444546	`[root@node1 mysql]``# cd init.d/` `[root@node1 init.d]``# vim mysqld_multi.server` `#!/bin/sh` `#` `# A simple startup script for mysqld_multi by Tim Smith and Jani Tolonen.` `# This script assumes that my.cnf file exists either in /etc/my.cnf or` `# /root/.my.cnf and has groups [mysqld_multi] and [mysqldN]. See the` `# mysqld_multi documentation for detailed instructions.` `#` `# This script can be used as /etc/init.d/mysql.server` `#` `# Comments to support chkconfig on RedHat Linux` `# chkconfig: 2345 64 36` `# description: A very fast and reliable SQL database engine.` `#` `# Version 1.0` `#``basedir=``/data/mysql` `bindir=``/data/mysql/bin``conf=``/data/mysql/etc/my``.cnf` `export``PATH=$PATH:$bindir``if``test``-x $bindir``/mysqld_multi` `then` `mysqld_multi=``"$bindir/mysqld_multi"``;` `else` `echo``"Can't execute $bindir/mysqld_multi from dir $basedir"``;` `exit``;` `fi``case``"$1"``in` `'start'``)` `"$mysqld_multi"``--defaults-extra-``file``=$conf start $2` `;;` `'stop'``)` `"$mysqld_multi"``--defaults-extra-``file``=$conf stop $2` `;;` `'report'``)` `"$mysqld_multi"``--defaults-extra-``file``=$conf report $2` `;;` `'restart'``)` `"$mysqld_multi"``--defaults-extra-``file``=$conf stop $2` `"$mysqld_multi"``--defaults-extra-``file``=$conf start $2` `;;` `*)` `echo``"Usage: $0 {start\|stop\|report\|restart}"``>&2` `;;` `esac`

好了，到这里我们所有的配置就全部完成了，下面我们打包备份一下。

八、整体备份方便后续迁移

1234567	`[root@node1 ~]``# cd /data/` `[root@node1 data]``# tar czvf mysql-5.5.350-full.tar.gz /data/mysql/``[root@node1 data]``# ll -h` `总用量 128M` `drwx------. 2 root root 16K 8月 17 18:42 lost+found` `drwxr-xr-x 22 mysql mysql 4.0K 1月 6 22:08 mysql` `-rw-r--r-- 1 root root 128M 1月 7 00:25 mysql-5.5.350-full.``tar``.gz`

注，备份完成后，直接将mysql-5.5.350-full.tar.gz拿到其他服务器上，解压后便可以直接启用。嘿嘿，方便吧……

九、管理MySQL多实例

1.同时启动四个mysql实例

(1).方法一：

1	`[root@node1 ~]``# /data/mysql/init.d/mysqld_multi.server start 1,2,3,4`

或方法二：

1	`[root@node1 ~]``# /data/mysql/init.d/mysqld_multi.server start 3306,3307,3308,3309`

(2).查看一下启动的实例

12345	`[root@node1 ~]``# netstat -ntulp \| grep mysqld` `tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 31416``/mysqld` `tcp 0 0 0.0.0.0:3308 0.0.0.0:* LISTEN 31414``/mysqld` `tcp 0 0 0.0.0.0:3309 0.0.0.0:* LISTEN 31420``/mysqld` `tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 31413``/mysqld`

2.同时关闭四个mysql实例

(1).方法一：

1	`[root@node1 ~]``# /data/mysql/init.d/mysqld_multi.server stop 1,2,3,4`

或方法二：

1	`[root@node1 ~]``# /data/mysql/init.d/mysqld_multi.server stop 3306,3307,3308,3309`

3.单独启动或关闭mysql实例

(1).启动一个实例

123	`[root@node1 ~]``# /data/mysql/init.d/mysqld_multi.server start 1``[root@node1 ~]``# netstat -ntulp \| grep mysqld` `tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 32221``/mysqld`

(2).关闭一个实例

1	`[root@node1 ~]``# /data/mysql/init.d/mysqld_multi.server stop 1`

注，启动或关闭两个或者三个实例方法的上面相同这里就不再演示。

十、登录MySQL多实例

注，我们同时启动四个实例，下面我们来演示一下怎么分别登录这四个实例。为了演示四个实例的区别，我们分别在四个实例中创建mydb1、mydb2、mydb3、mydb4。

1.登录[mysqld1]

123456789101112131415161718192021222324252627282930313233	`[root@node1 ~]``# /data/mysql/bin/mysql -uroot -h127.0.0.1 -P3306 -p` `Enter password:` `Welcome to the MySQL monitor. Commands end with ; or \g.` `Your MySQL connection` `id``is 1` `Server version: 5.5.35-log Source distribution``Copyright (c) 2000, 2013, Oracle and``/or``its affiliates. All rights reserved.``Oracle is a registered trademark of Oracle Corporation and``/or``its` `affiliates. Other names may be trademarks of their respective` `owners.``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> create database mydb1;` `Query OK, 1 row affected (0.00 sec)``mysql> show databases;` `+--------------------+` `\| Database \|` `+--------------------+` `\| information_schema \|` `\| mydb1 \|` `\| mysql \|` `\| performance_schema \|` `\|` `test``\|` `+--------------------+` `5 rows` `in``set``(0.00 sec)`

2.登录[mysqld2]

123456789101112131415161718192021222324252627282930313233	`[root@node1 ~]``# /data/mysql/bin/mysql -uroot -h127.0.0.1 -P3307 -p` `Enter password:` `Welcome to the MySQL monitor. Commands end with ; or \g.` `Your MySQL connection` `id``is 1` `Server version: 5.5.35-log Source distribution``Copyright (c) 2000, 2013, Oracle and``/or``its affiliates. All rights reserved.``Oracle is a registered trademark of Oracle Corporation and``/or``its` `affiliates. Other names may be trademarks of their respective` `owners.``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> create database mydb2;` `Query OK, 1 row affected (0.00 sec)``mysql> show databases;` `+--------------------+` `\| Database \|` `+--------------------+` `\| information_schema \|` `\| mydb2 \|` `\| mysql \|` `\| performance_schema \|` `\|` `test``\|` `+--------------------+` `5 rows` `in``set``(0.00 sec)`

3.登录[mysqld3]

123456789101112131415161718192021222324252627282930313233	`[root@node1 ~]``# /data/mysql/bin/mysql -uroot -h127.0.0.1 -P3308 -p` `Enter password:` `Welcome to the MySQL monitor. Commands end with ; or \g.` `Your MySQL connection` `id``is 1` `Server version: 5.5.35-log Source distribution``Copyright (c) 2000, 2013, Oracle and``/or``its affiliates. All rights reserved.``Oracle is a registered trademark of Oracle Corporation and``/or``its` `affiliates. Other names may be trademarks of their respective` `owners.``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.01 sec)``mysql> create database mydb3;` `Query OK, 1 row affected (0.00 sec)``mysql> show databases;` `+--------------------+` `\| Database \|` `+--------------------+` `\| information_schema \|` `\| mydb3 \|` `\| mysql \|` `\| performance_schema \|` `\|` `test``\|` `+--------------------+` `5 rows` `in``set``(0.00 sec)`

4.登录[mysqld4]

123456789101112131415161718192021222324252627282930313233	`[root@node1 ~]``# /data/mysql/bin/mysql -uroot -h127.0.0.1 -P3309 -p` `Enter password:` `Welcome to the MySQL monitor. Commands end with ; or \g.` `Your MySQL connection` `id``is 1` `Server version: 5.5.35-log Source distribution``Copyright (c) 2000, 2013, Oracle and``/or``its affiliates. All rights reserved.``Oracle is a registered trademark of Oracle Corporation and``/or``its` `affiliates. Other names may be trademarks of their respective` `owners.``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.01 sec)``mysql> create database mydb4;` `Query OK, 1 row affected (0.00 sec)``mysql> show databases;` `+--------------------+` `\| Database \|` `+--------------------+` `\| information_schema \|` `\| mydb4 \|` `\| mysql \|` `\| performance_schema \|` `\|` `test``\|` `+--------------------+` `5 rows` `in``set``(0.00 sec)`

好了，我们的MySQL多实例登录就演示到这里了，下面我们来设置一下mysql的root密码。

十一、其它管理配置

1.为mysql的root用户创建密码

1234	`[root@node1 ~]``# /data/mysql/bin/mysqladmin -uroot -h127.0.0.1 -P3306 password '123456'``[root@node1 ~]``# /data/mysql/bin/mysqladmin -uroot -h127.0.0.1 -P3307 password '123456'``[root@node1 ~]``# /data/mysql/bin/mysqladmin -uroot -h127.0.0.1 -P3308 password '123456'``[root@node1 ~]``# /data/mysql/bin/mysqladmin -uroot -h127.0.0.1 -P3309 password '123456'`

2.删除匿名连接的空密码帐号

注，分别登录实例[mysqld1]、[mysqld2]、[mysqld3]、[mysqld4]，执行以下命令：

123456	`mysql>use mysql;` `#选择系统数据库mysql` `mysql>``select``Host,User,Password from user;` `#查看所有用户` `mysql>delete from user where password=``""``;``#删除无密码账户` `mysql>flush privileges;` `#刷新权限` `mysql>``select``Host,User,Password from user;` `#确认密码为空的用户是否已全部删除` `mysql>``exit``;`

十二、总结

1.采用源码编译安装MySQL，可能在第一次会花费较多的时间，但却是非常值得的，因为我们可以自己组织所有MySQL相关文件的位置；并且经过源码编译安装后的MySQL，可以直接复制到其它服务器上运行，大大方便了我们以后的迁移、备份和新服务器的配置。

2.本文中仅仅用了四个实例[mysqld1]、[mysqld2]、[mysqld3]、[mysqld4]来举例，实际上我们可以通过这样的方式，实现[mysqld5]、[mysqld6]...等更多的实例，前提是你的服务器硬件配置得根得上，但是一般我们这边不会超过6个实例。

3.在单机运行多实例的情况下，切忌不要使用 mysql -hlocalhost 或直接忽略-h参数登录服务器，这应该算是MySQL的一个bug，就是如果使用localhost或忽略-h参数，而不是指定127.0.0.1的话，即使选择的端口是3307，还是会登陆到3306中去，因此应尽量避免这种混乱的产生，统一用127.0.0.1绑定端口或采用socket来登录，在mysql5.5中你不指定-h127.0.0.1选项，你是无法登录的。

最后，希望大家有所收获吧^_^……

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。