本次采用的是Centos6系统安装MySQL5.6.29数据库进行演示，一种方式是采用默认配置my.cnf来管理，另外一种方式是安装分目录my.cnf方式来管理。
mysql
采用默认配置my.cnf
一、Mysql-5.6.29源码安装准备：
1，安装需要的软件
[root@localhost ~]#yum -y install gcc gcc-c++ make ncurses ncurses-devel cmake cmake二、源码安装mysql-5.6.29
2.1)创建用户和组并创建数据存放目录
[root@localhost ~]#groupadd mysql[root@localhost ~]#useradd mysql -s /sbin/nologin -M -g mysql[root@localhost mysql-5.6.29]# mkdir -p /data/mysql_3306[root@localhost mysql-5.6.29]# mkdir -p /data/mysql_3307[root@localhost mysql-5.6.29]# mkdir -p /data/mysql_33082.2)给数据库配置环境变量
[root@localhost mysql-5.6.29]#echo"export PATH=$PATH:/usr/local/mysql/bin:/usr/local/mysql/lib" >>/etc/profile[root@localhost mysql-5.6.29]#source /etc/profile[root@localhost mysql-5.6.29]#echo $PATH2.3)解压安装mysql-5.6.29
[root@localhost ~]#tar xf mysql-5.6.29.tar.gz[root@localhost mysql-5.6.29]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/usr/local/mysql/data -DMYSQL_UNIX_ADDR=/usr/local/mysql/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=0 -DWITH_FAST_MUTEXES=1 -DWITH_ZLIB=bundled -DENABLED_LOCAL_INFILE=1 -DWITH_READLINE=1 -DWITH_EMBEDDED_SERVER=1 -DWITH_DEBUG=0[root@localhost mysql-5.6.29]# make [root@localhost mysql-5.6.29]# make install2.4)给数据库目录授权
三、配置数据库
3.1)配置文件my.cnf文件
[root@localhost mysql-5.6.29]#vim /etc/my.cnf[root@mysqldb bin]# cat /etc/my.cnf [client]#port = 3306#socket = /tmp/mysql.sock[mysqld3306]port = 3306basedir = /usr/local/mysqldatadir = /data/mysql_3306socket = /tmp/mysql_3306.sockslow_query_log_file = /data/mysql_3306/slow.loglog-error = /data/mysql_3306/error.loglog-bin = /data/mysql_3306/mysql-binsync_binlog = 1binlog_cache_size = 4Mdefault-storage-engine = InnoDBbinlog_format = rowtransaction_isolation = REPEATABLE-READinnodb_buffer_pool_size = 100m[mysql3307]port = 3307basedir = /usr/local/mysqldatadir = /data/mysql_3307socket = /tmp/mysql_3307.sockslow_query_log_file = /data/mysql_3307/slow.loglog-error = /data/mysql_3307/error.loglog-bin = /data/mysql_3307/mysql-binsync_binlog = 1binlog_cache_size = 4Mdefault-storage-engine = InnoDBbinlog_format = rowtransaction_isolation = REPEATABLE-READinnodb_buffer_pool_size = 100m[mysqld3308]port = 3308basedir = /usr/local/mysqldatadir = /data/mysql_3308socket = /tmp/mysql_3308.sockslow_query_log_file = /data/mysql_3308/slow.loglog-error = /data/mysql_3308/error.loglog-bin = /data/mysql_3308/mysql-binsync_binlog = 1binlog_cache_size = 4Mdefault-storage-engine = InnoDBbinlog_format = rowtransaction_isolation = REPEATABLE-READinnodb_buffer_pool_size = 100m[mysqld_multi]user = rootpassword = hwg123mysqld=/usr/local/mysql/bin/mysqld_safemysqladmin=/usr/local/mysql/bin/mysqladmin [mysqldump]quickmax_allowed_packet = 32M3.2)初始化数据库文件
[root@mysqldb ~]# cd /usr/local/mysql/scripts/[root@mysqldb scripts]# ./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql_3306 --defaults-file=/etc/my.cnf --user=mysql[root@mysqldb scripts]# ./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql_3307 --defaults-file=/etc/my.cnf --user=mysql[root@mysqldb scripts]# ./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql_3308 --defaults-file=/etc/my.cnf --user=mysql3.3)多实例采用mysqld_multi来启停数据库
[root@mysqldb bin]# ./mysqld_multi --defaults-file=/etc/my.cnf --user=root --password=hwg123 start 3306,3307,33083.4)查看多实例运行的状态
[root@mysqldb bin]# ./mysqld_multi reportReporting MySQL serversMySQL server from group: mysqld3306 is runningMySQL server from group: mysqld3307 is runningMySQL server from group: mysqld3308 is running四、登录数据库操作
4.1)登录数据库是没有密码，因此需要修改密码
[root@mysqldb bin]# mysql -uroot -p -S /tmp/mysql_3306.sockEnter password: Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 9Server version: 5.6.29-log Source distributionCopyright (c) 2000, 2016, 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> use mysql;mysql>UPDATE user SET password=PASSWORD('hwg123') WHERE user='root';mysql>FLUSH PRIVILEGES;[root@mysqldb bin]# mysql -uroot -p -S /tmp/mysql_3308.sockmysql> use mysql;mysql>UPDATE user SET password=PASSWORD('hwg123') WHERE user='root';mysql>FLUSH PRIVILEGES;4.2)授权某个网段ip登录到本机
先建一个库db01测试用一下：
mysql> create database db01;mysql> CREATE TABLE `goods` ( `gid` char(10) NOT NULL, `gnam` varchar(20) DEFAULT NULL, `gdate` date DEFAULT NULL, `gprice` char(100) DEFAULT '0', `gvprice` varchar(100) DEFAULT NULL, PRIMARY KEY (`gid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;mysql> show tables;mysql> desc goods;再创建一个用户tony用来远程连接数据库：
mysql> grant all on db01.* to 'tony'@'10.93.58.%'identified by 'hwg123'WITH GRANT OPTION;mysql> flush privileges;另外一个3307数据库也是一样的设置：
mysql> create database db02;mysql> CREATE TABLE `goods` ( `gid` char(10) NOT NULL, `gnam` varchar(20) DEFAULT NULL, `gdate` date DEFAULT NULL, `gprice` char(100) DEFAULT '0', `gvprice` varchar(100) DEFAULT NULL, PRIMARY KEY (`gid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;mysql> show tables;mysql> desc goods;再创建一个用户tony用来远程连接数据库：
mysql> grant all on db02.* to 'tony'@'10.93.58.%'identified by 'hwg123'WITH GRANT OPTION;mysql> flush privileges;五、开启和停止数据库
[root@mysqldb bin]# mysqld_multi stop 3306此时发现我们不能正常的关闭某个数据库，查询资料需要修改my.cnf和mysqld_multi参数
[root@mysqldb bin]#vim /etc/my.cnf增加如下：[mysqld_multi]user = rootpassword = hwg123[root@mysqldb bin]# vim /usr/local/mysql/bin/mysqld_multi修改如下一条记录：my_print_defaults -smy $com= join ' ', 'my_print_defaults -s', @defaults_options, $group;修改后我们就可以正常的停止启用数据库：
[root@mysqldb bin]# mysqld_multi stop 3307[root@mysqldb bin]# mysqld_multi reportReporting MySQL serversMySQL server from group: mysqld3306 is runningMySQL server from group: mysqld3307 is not runningMySQL server from group: mysqld3308 is running安装分目录my.cnf方式
一，mysql-5.6.29源码安装准备：
1，安装需要的软件
[root@localhost ~]#yum -y install gcc gcc-c++ make ncurses ncurses-devel cmake cmake二，源码安装mysql-5.6.29
2.1)创建用户和组并创建数据存放目录
[root@localhost ~]#groupadd mysql[root@localhost ~]#useradd mysql -s /sbin/nologin -M -g mysql[root@localhost mysql-5.6.29]# mkdir -p /data/mysql_3306[root@localhost mysql-5.6.29]# mkdir -p /data/mysql_3307[root@localhost mysql-5.6.29]# mkdir -p /data/mysql_33082.2)给数据库配置环境变量
[root@localhost mysql-5.6.29]#echo"export PATH=$PATH:/usr/local/mysql/bin:/usr/local/mysql/lib" >>/etc/profile[root@localhost mysql-5.6.29]#source /etc/profile[root@localhost mysql-5.6.29]#echo $PATH2.3)解压安装mysql-5.6.29
[root@localhost ~]#tar xf mysql-5.6.29.tar.gz[root@localhost mysql-5.6.29]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/usr/local/mysql/data -DMYSQL_UNIX_ADDR=/usr/local/mysql/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=0 -DWITH_FAST_MUTEXES=1 -DWITH_ZLIB=bundled -DENABLED_LOCAL_INFILE=1 -DWITH_READLINE=1 -DWITH_EMBEDDED_SERVER=1 -DWITH_DEBUG=0[root@localhost mysql-5.6.29]# make [root@localhost mysql-5.6.29]# make install2.4)给数据库目录授权
三、配置数据库
3.1)配置文件my.cnf文件
[root@mysqldb data]# lslost+found mysql_3306 mysql_3307 mysql_3308分别在3306、3307、3308三个目录下面建立文件夹并且命名为my_3306.cnf、my_3307.cnf、my_3308.cnf
这三个文件夹的配置文件内容如下：
my_3306.cnf配置文件如下：
[root@mysqldb mysql_3306]#vim /data/mysql_3306/my_3306.cnf[client]#port = 3306#socket = /tmp/mysql.sock[mysqld3306]port = 3306basedir = /usr/local/mysqldatadir = /data/mysql_3306socket = /tmp/mysql_3306.sockslow_query_log_file = /data/mysql_3306/slow.loglog-error = /data/mysql_3306/error.loglog-bin = /data/mysql_3306/mysql-binsync_binlog = 1server_id = 1binlog_cache_size = 4Mdefault-storage-engine = InnoDBbinlog_format = rowtransaction_isolation = REPEATABLE-READinnodb_buffer_pool_size = 100m[mysqld_multi]user = rootpassword = hwg123#mysqld = /usr/local/mysql/bin/mysqld_safe#mysqladmin = /usr/local/mysql/bin/mysqladmin log = /usr/local/mysql/data/mysql_multi.log[mysqldump]quickmax_allowed_packet = 32Mmy_3307.cnf配置文件如下：[root@mysqldb mysql_3307]# vim my_3307.cnf [client]#port = 3307#socket = /tmp/mysql.sock[mysqld3307]port = 3307basedir = /usr/local/mysqldatadir = /data/mysql_3307socket = /tmp/mysql_3307.sockslow_query_log_file = /data/mysql_3307/slow.loglog-error = /data/mysql_3307/error.loglog-bin = /data/mysql_3307/mysql-binsync_binlog = 1server_id = 3307binlog_cache_size = 4Mdefault-storage-engine = InnoDBbinlog_format = rowtransaction_isolation = REPEATABLE-READinnodb_buffer_pool_size = 100m[mysqld_multi]user = rootpassword = hwg123#mysqld = /usr/local/mysql/bin/mysqld_safe##mysqladmin = /usr/local/mysql/bin/mysqladmin log = /usr/local/mysql/data/mysql_multi.log[mysqldump]quickmax_allowed_packet = 32Mmy_3308.cnf配置文件如下：[root@mysqldb mysql_3308]# vi my_3308.cnf [client]#port = 3308#socket = /tmp/mysql.sock[mysqld3308]port = 3308basedir = /usr/local/mysqldatadir = /data/mysql_3308socket = /tmp/mysql_3308.sockslow_query_log_file = /data/mysql_3308/slow.loglog-error = /data/mysql_3308/error.loglog-bin = /data/mysql_3308/mysql-binsync_binlog = 1server_id = 3308binlog_cache_size = 4Mdefault-storage-engine = InnoDBbinlog_format = rowtransaction_isolation = REPEATABLE-READinnodb_buffer_pool_size = 100m[mysqld_multi]user = rootpassword = hwg123#mysqld = /usr/local/mysql/bin/mysqld_safe#mysqladmin = /usr/local/mysql/bin/mysqladminlog = /usr/local/mysql/data/mysql_multi.log[mysqldump]quickmax_allowed_packet = 32M3.2)初始化数据库文件
[root@mysqldb ~]# cd /usr/local/mysql/scripts/[root@mysqldb scripts]# ./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql_3306 --defaults-file=/data/mysql_3306/my_3306.cnf --user=mysql[root@mysqldb scripts]# ./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql_3307 --defaults-file=/data/mysql_3307/my_3307.cnf --user=mysql[root@mysqldb scripts]# ./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql_3308 --defaults-file=/data/mysql_3308/my_3308.cnf--user=mysql3.3)多实例采用mysqld_multi来启停数据库
[root@mysqldb bin]# ./mysqld_multi --defaults-file=/data/mysql_3306/my_3306.cnf --user=root start 3306[root@mysqldb bin]# ./mysqld_multi --defaults-file=/data/mysql_3307/my_3307.cnf --user=root start 3307[root@mysqldb bin]# ./mysqld_multi --defaults-file=/data/mysql_3308/my_3308.cnf --user=root start 33083.4)查看多实例运行的状态
[root@mysqldb bin]# ./mysqld_multi --defaults-file=/data/mysql_3306/my_3306.cnf report Reporting MySQL serversMySQL server from group: mysqld3306 is running[root@mysqldb bin]# ./mysqld_multi --defaults-file=/data/mysql_3307/my_3307.cnf report Reporting MySQL serversMySQL server from group: mysqld3307 is running[root@mysqldb bin]# ./mysqld_multi --defaults-file=/data/mysql_3308/my_3308.cnf report Reporting MySQL serversMySQL server from group: mysqld3308 is running[root@mysqldb bin]# netstat -lantup |grep 330tcp 0 0 :::3307 :::* LISTEN 11501/mysqld tcp 0 0 :::3308 :::* LISTEN 11443/mysqld tcp 0 0 :::3306 :::* LISTEN 11475/mysqld 四，登录数据库操作
4.1)登录数据库是没有密码，因此需要修改密码
[root@mysqldb bin]# mysql -uroot -p -S /tmp/mysql_3306.sock[root@mysqldb bin]# mysql -uroot -p -S /tmp/mysql_3307.sock[root@mysqldb bin]# mysql -uroot -p -S /tmp/mysql_3308.sock登录一台数据库修改密码操作如下：
mysql> use mysql;mysql>UPDATE user SET password=PASSWORD('hwg123') WHERE user='root';mysql>FLUSH PRIVILEGES;4.2)授权某个网段ip登录到本机
登录一台数据库操作如下：先建一个库db01
mysql> create database db01;mysql> CREATE TABLE `goods` ( `gid` char(10) NOT NULL, `gnam` varchar(20) DEFAULT NULL, `gdate` date DEFAULT NULL, `gprice` char(100) DEFAULT '0', `gvprice` varchar(100) DEFAULT NULL, PRIMARY KEY (`gid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;mysql> show tables;mysql> desc goods;再创建一个用户tony用来远程连接数据库：
mysql> grant all on db01.* to 'tony'@'10.93.58.%'identified by 'hwg123'WITH GRANT OPTION;mysql> flush privileges;五、开启、停止数据库
5.1)开启数据库
[root@mysqldb bin]# ./mysqld_multi --defaults-file=/data/mysql_3306/my_3306.cnf --user=root start 3306[root@mysqldb bin]# ./mysqld_multi --defaults-file=/data/mysql_3307/my_3307.cnf --user=root start 3307[root@mysqldb bin]# ./mysqld_multi --defaults-file=/data/mysql_3308/my_3308.cnf --user=root start 33085.2)停止数据库
[root@mysqldb bin]# ./mysqld_multi --defaults-file=/data/mysql_3306/my_3306.cnf --user=root stop 3306 [root@mysqldb bin]# ./mysqld_multi --defaults-file=/data/mysql_3307/my_3307.cnf --user=root stop 3307[root@mysqldb bin]# ./mysqld_multi --defaults-file=/data/mysql_3308/my_3308.cnf --user=root stop 3308 总结
1，主要的区别是一个采用默认my.cnf来管理数据库，另外一个在建立的文件夹内分别新建配置文件分开管理数据库。
2，适合使用一个数据库服务器上跑多个应用，节约使用成本达到最大利用率。
3，本人有在CentOS6系统上安装过，效果还不错，你若喜欢可以试试。
4，欢迎评论留言，最后感谢你的观看，谢谢！
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
