编程开源技术交流，分享技术与知识

网站首页 > 开源技术 正文

运维人员需要掌握MySQL数据库不一样启动方式

wxchong 2024-07-20 08:45:24 开源技术 13 ℃ 0 评论

本次采用的是Centos6系统安装MySQL5.6.29数据库进行演示，一种方式是采用默认配置my.cnf来管理，另外一种方式是安装分目录my.cnf方式来管理。

采用默认配置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_3308

2.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 $PATH

2.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 install

2.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 = 3306
basedir = /usr/local/mysql
datadir = /data/mysql_3306
socket = /tmp/mysql_3306.sock
slow_query_log_file = /data/mysql_3306/slow.log
log-error = /data/mysql_3306/error.log
log-bin = /data/mysql_3306/mysql-bin
sync_binlog = 1
binlog_cache_size = 4M
default-storage-engine = InnoDB
binlog_format = row
transaction_isolation = REPEATABLE-READ
innodb_buffer_pool_size = 100m
[mysql3307]
port = 3307
basedir = /usr/local/mysql
datadir = /data/mysql_3307
socket = /tmp/mysql_3307.sock
slow_query_log_file = /data/mysql_3307/slow.log
log-error = /data/mysql_3307/error.log
log-bin = /data/mysql_3307/mysql-bin
sync_binlog = 1
binlog_cache_size = 4M
default-storage-engine = InnoDB
binlog_format = row
transaction_isolation = REPEATABLE-READ
innodb_buffer_pool_size = 100m
[mysqld3308]
port = 3308
basedir = /usr/local/mysql
datadir = /data/mysql_3308
socket = /tmp/mysql_3308.sock
slow_query_log_file = /data/mysql_3308/slow.log
log-error = /data/mysql_3308/error.log
log-bin = /data/mysql_3308/mysql-bin
sync_binlog = 1
binlog_cache_size = 4M
default-storage-engine = InnoDB
binlog_format = row
transaction_isolation = REPEATABLE-READ
innodb_buffer_pool_size = 100m
[mysqld_multi]
user = root
password = hwg123
mysqld=/usr/local/mysql/bin/mysqld_safe
mysqladmin=/usr/local/mysql/bin/mysqladmin 
[mysqldump]
quick
max_allowed_packet = 32M

3.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=mysql

3.3）多实例采用mysqld_multi来启停数据库

[root@mysqldb bin]# ./mysqld_multi --defaults-file=/etc/my.cnf --user=root --password=hwg123 start 3306,3307,3308

3.4)查看多实例运行的状态

[root@mysqldb bin]# ./mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running

四、登录数据库操作

4.1)登录数据库是没有密码，因此需要修改密码

[root@mysqldb bin]# mysql -uroot -p -S /tmp/mysql_3306.sock
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.6.29-log Source distribution
Copyright (c) 2000, 2016, 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> 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.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;

另外一个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 = root
password = hwg123
[root@mysqldb bin]# vim /usr/local/mysql/bin/mysqld_multi
修改如下一条记录：my_print_defaults -s
my $com= join ' ', 'my_print_defaults -s', @defaults_options, $group;

修改后我们就可以正常的停止启用数据库：

[root@mysqldb bin]# mysqld_multi stop 3307
[root@mysqldb bin]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is not running
MySQL 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_3308

2.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 $PATH

2.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 install

2.4)给数据库目录授权

三、配置数据库

3.1）配置文件my.cnf文件

[root@mysqldb data]# ls
lost+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 = 3306
basedir = /usr/local/mysql
datadir = /data/mysql_3306
socket = /tmp/mysql_3306.sock
slow_query_log_file = /data/mysql_3306/slow.log
log-error = /data/mysql_3306/error.log
log-bin = /data/mysql_3306/mysql-bin
sync_binlog = 1
server_id = 1
binlog_cache_size = 4M
default-storage-engine = InnoDB
binlog_format = row
transaction_isolation = REPEATABLE-READ
innodb_buffer_pool_size = 100m
[mysqld_multi]
user = root
password = hwg123
#mysqld = /usr/local/mysql/bin/mysqld_safe
#mysqladmin = /usr/local/mysql/bin/mysqladmin 
log = /usr/local/mysql/data/mysql_multi.log
[mysqldump]
quick
max_allowed_packet = 32M
my_3307.cnf配置文件如下：
[root@mysqldb mysql_3307]# vim my_3307.cnf 
[client]
#port = 3307
#socket = /tmp/mysql.sock
[mysqld3307]
port = 3307
basedir = /usr/local/mysql
datadir = /data/mysql_3307
socket = /tmp/mysql_3307.sock
slow_query_log_file = /data/mysql_3307/slow.log
log-error = /data/mysql_3307/error.log
log-bin = /data/mysql_3307/mysql-bin
sync_binlog = 1
server_id = 3307
binlog_cache_size = 4M
default-storage-engine = InnoDB
binlog_format = row
transaction_isolation = REPEATABLE-READ
innodb_buffer_pool_size = 100m
[mysqld_multi]
user = root
password = hwg123
#mysqld = /usr/local/mysql/bin/mysqld_safe
##mysqladmin = /usr/local/mysql/bin/mysqladmin 
log = /usr/local/mysql/data/mysql_multi.log
[mysqldump]
quick
max_allowed_packet = 32M
my_3308.cnf配置文件如下：
[root@mysqldb mysql_3308]# vi my_3308.cnf 
[client]
#port = 3308
#socket = /tmp/mysql.sock
[mysqld3308]
port = 3308
basedir = /usr/local/mysql
datadir = /data/mysql_3308
socket = /tmp/mysql_3308.sock
slow_query_log_file = /data/mysql_3308/slow.log
log-error = /data/mysql_3308/error.log
log-bin = /data/mysql_3308/mysql-bin
sync_binlog = 1
server_id = 3308
binlog_cache_size = 4M
default-storage-engine = InnoDB
binlog_format = row
transaction_isolation = REPEATABLE-READ
innodb_buffer_pool_size = 100m
[mysqld_multi]
user = root
password = hwg123
#mysqld = /usr/local/mysql/bin/mysqld_safe
#mysqladmin = /usr/local/mysql/bin/mysqladmin
log = /usr/local/mysql/data/mysql_multi.log
[mysqldump]
quick
max_allowed_packet = 32M

3.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=mysql

3.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 3308

3.4)查看多实例运行的状态

[root@mysqldb bin]# ./mysqld_multi --defaults-file=/data/mysql_3306/my_3306.cnf report 
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
[root@mysqldb bin]# ./mysqld_multi --defaults-file=/data/mysql_3307/my_3307.cnf report 
Reporting MySQL servers
MySQL server from group: mysqld3307 is running
[root@mysqldb bin]# ./mysqld_multi --defaults-file=/data/mysql_3308/my_3308.cnf report 
Reporting MySQL servers
MySQL server from group: mysqld3308 is running
[root@mysqldb bin]# netstat -lantup |grep 330
tcp 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 3308

5.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，欢迎评论留言，最后感谢你的观看，谢谢！

Tags：

猜你喜欢

本文暂时没有评论，来添加一个吧(●'◡'●)

欢迎 你 发表评论:

最近发表
标签列表
赣ICP备2023011894号-2