mysql创建不同端口实例
1、复制cnf文件
cd /usr/local/mysql/etc
cp my_3308.cnf my_3322.cnf
或:cp /usr/local/mysql/etc/my_3308.cnf /usr/local/mysql/etc/my.3322.cnf
2、修改cnf信息
[client]
port = 3322
socket = /usr/local/mysql/tmp/mysql3322.sock
[mysqld]
user = mysql
port = 3322
basedir = /usr/local/mysql
datadir = /mysql_3322/data
socket = /usr/local/mysql/tmp/mysql3322.sock
pid-file = mysql3322.pid
slow_query_log_file = /mysql_3322/slow.log
log-error = /mysql_3322/error.log
server-id = 3322
log-bin = /mysql_3322/bin_log/binlog
#skip-grant-tables =1
3、创建数据库指定存放数据的目录及日志目录
/mysql_3322/bin_log
/mysql_3322/data
/mysql_3322/error.log
/mysql_3322/slow.log
4、授权
chown mysql:mysql my_3322.cnf
chown -R mysql:mysql mysql_3322
5、初始化数据库
./mysqld --defaults-file=/usr/local/mysql/etc/my_3322.cnf --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/mysql_3322/data
PS:如果初始化不成功,data里面的文件需要清空
6、启动mysql
查看error.log密码:
./mysqld_safe --defaults-file=/usr/local/mysql/etc/my_3322.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/mysql_3322/data
7、连接数据库
两种连接方式:
1)使用sock文件连接
mysql -uroot -p -S /usr/local/mysql/tmp/mysql3322.sock
2)使用端口号连接
mysql -h 127.0.0.1 -uroot -p -P3322
8、修改 my_3322.cnf
去掉注释
skip-grant-tables =1
重新启动,不使用密码登录
查看数据库:
show databases;
选中mysql数据库:
use mysql;
查看user表:
show create table user\G;
在user表中查看user ,host ,password_expired:
select user ,host ,password_expired from user;
修改root用户的password_expired的值为'N':
update user set password_expired='N' where user='root';
退出:
exit
9、重新启动
如果是使用safe方式启动的服务,需要将对应的safe进程杀掉,然后在关闭服务
查询mysql的进程:
ps -ef|grep mysql
kill -9 safe 的进程号
关闭mysql:
/usr/local/mysql/bin/mysqladmin -uroot -p -P3322 -S /usr/local/mysql/tmp/mysql3322.sock shutdown
查看error.log里面的密码(最下面的那个)
cat /mysql_3322/error.log |grep root@'localhost'
PS:如果有多个密码记录,选择最下面的那个
使用生成的密码登录
两种连接方式:
1)使用sock文件连接
mysql -uroot -p -S /usr/local/mysql/tmp/mysql3322.sock
2)使用端口号连接
mysql -h 127.0.0.1 -uroot -p -P3322
10、修改登录密码和用户权限
修改用户密码:
alter user 'root'@'localhost' identified by 'qwe!@#123';
授权新的用户:
grant all privileges on *.* to dble@'%' identified by '123456' with grant option;
刷新:
flush privileges
退出
exit
11、修改 my_3322.cnf
#skip-grant-tables =1
重新启动,使用新密码登录
参考博客
https://blog.csdn.net/m0_37695414/article/details/82147004