【mysql】mysql安装部署以及用户误删恢复
本站以分享各种运维经验和运维所需要的技能为主
《python零基础入门》：python零基础入门学习
《python运维脚本》： python运维脚本实践
《shell》：shell学习
《terraform》持续更新中：terraform_Aws学习零基础入门到最佳实战
《k8》从问题中去学习k8s
《docker学习》暂未更新
《ceph学习》ceph日常问题解决分享
《日志收集》ELK+各种中间件
《运维日常》运维日常
《linux》运维面试100问
《DBA》db的介绍使用（mysql、redis、mongodb...）
一、mysql安装
1.二进制安装 2.源码包安装 3.rpm包安装
1.源码包安装
1）上传或下载源码包
[root@db02 ~]# rz mysql-5.6.46.tar.gz
2）安装依赖
[root@db02 ~]# yum install -y ncurses-devel libaio-devel gcc gcc-c++ glibc cmake autoconf openssl openssl-devel
3）解压安装包
[root@db02 ~]# tar xf mysql-5.6.46.tar.gz
4）生成
[root@db02 ~]# cd mysql-5.6.46/ [root@db02 mysql-5.6.46]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.6.46 \ -DMYSQL_DATADIR=/usr/local/mysql-5.6.46/data \ -DMYSQL_UNIX_ADDR=/usr/local/mysql-5.6.46/tmp/mysql.sock \ -DDEFAULT_CHARSET=utf8 \ -DDEFAULT_COLLATION=utf8_general_ci \ -DWITH_EXTRA_CHARSETS=all \ -DWITH_INNOBASE_STORAGE_ENGINE=1 \ -DWITH_FEDERATED_STORAGE_ENGINE=1 \ -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \ -DWITH_ZLIB=bundled \ -DWITH_SSL=system \ -DENABLED_LOCAL_INFILE=1 \ -DWITH_EMBEDDED_SERVER=1 \ -DENABLE_DOWNLOADS=1 \ -DWITH_DEBUG=0
5）编译和安装
[root@db02 mysql-5.6.46]# make && make install
6）做软连接
[root@db02 ~]# ln -s /usr/local/mysql-5.6.46 /usr/local/mysql
7）创建用户
[root@db02 ~]# useradd mysql -s /sbin/nologin -M
8）拷贝配置文件和启动脚本
[root@db02 ~]# cd /usr/local/mysql/support-files/ [root@db02 support-files]# cp my-default.cnf /etc/my.cnf cp: overwrite '/etc/my.cnf'? y [root@db02 support-files]# cp mysql.server /etc/init.d/mysqld
9）初始化数据库
[root@db02 support-files]# cd /usr/local/mysql/scripts/ [root@db02 scripts]# ./mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
8）配置system管理mysql
[root@db02 scripts]# vim /usr/lib/systemd/system/mysqld.service [Unit] Description=MySQL Server Documentation=man:mysqld(8) Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.html After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/usr/local/mysql/bin --defaults-file=/etc/my.cnf LimitNOFILE = 5000[root@db02 scripts]# systemctl daemon-reload [root@db02 scripts]# systemctl start mysqld
9）启动失败报错
[root@db02 scripts]# /etc/init.d/mysqld start Starting MySQL.Logging to '/usr/local/mysql-5.6.46/data/db02.err'. 200709 15:42:14 mysqld_safe Directory '/usr/local/mysql-5.6.46/tmp' for UNIX socket file don't exists.ERROR! The server quit without updating PID file (/usr/local/mysql-5.6.46/data/db02.pid).#原因： 1.cmake过程指定了socket文件位置，实际位置不存在 2.目录权限不足#解决： [root@db02 scripts]# mkdir /usr/local/mysql-5.6.46/tmp/ [root@db02 scripts]# chown -R mysql.mysql /usr/local/mysql [root@db02 scripts]# chown -R mysql.mysql /usr/local/mysql-5.6.46/
10）启动数据库
[root@db02 scripts]# systemctl start mysqld
11）配置环境变量
[root@db03 scripts]# vim /etc/profile.d/mysql.sh export PATH=/usr/local/mysql/bin:$PATH[root@db03 mysql]# source /etc/profile
12）确认启动
[root@m01 scripts]# ps -ef | grep mysql mysql 12886 1 2 03:10 ? 00:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf root 12921 10636 0 03:11 pts/1 00:00:00 grep --color=auto mysql [root@m01 scripts]# netstat -lntp tcp6 0 0 :::3306 :::* LISTEN 12886/mysqld
2.二进制安装mysql（指定安装目录）
1）安装依赖
[root@m01 ~]# yum install -y ncurses-devel libaio-devel gcc gcc-c++ glibc cmake autoconf openssl openssl-devel
2）解压包
[root@db03 ~]# tar xf mysql-5.6.46-linux-glibc2.12-x86_64.tar.gz
3）创建自定义目录
[root@db03 ~]# mkdir /service
4）移动目录并做软连接
[root@db03 ~]# mv mysql-5.6.46-linux-glibc2.12-x86_64 /service/ [root@db03 ~]# ln -s /service/mysql-5.6.46-linux-glibc2.12-x86_64 /service/mysql
5）创建用户
[root@db03 ~]# useradd mysql -s /sbin/nologin -M
6）拷贝配置文件和启动脚本
[root@db03 ~]# cd /service/mysql/support-files/ [root@db03 support-files]# cp my-default.cnf /etc/my.cnf cp: overwrite '/etc/my.cnf'? y [root@db03 support-files]# cp mysql.server /etc/init.d/mysqld
7）初识化数据库
[root@db03 ~]# cd /usr/local/mysql/scripts/ [root@db03 scripts]# ./mysql_install_db --user=mysql --basedir=/service/mysql --datadir=/service/mysql/data
8）配置system管理mysql
[root@db03 scripts]# vim /usr/lib/systemd/system/mysqld.service [Unit] Description=MySQL Server Documentation=man:mysqld(8) Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.html After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/service/mysql/bin/mysqld --defaults-file=/etc/my.cnf LimitNOFILE = 5000[root@db03 scripts]# systemctl daemon-reload [root@db03 scripts]# systemctl start mysqld
9）启动数据库
[root@m01 scripts]# /etc/init.d/mysqld start#启动失败，因为mysql文件中很多都是/usr/local/mysql，我们需要替换 [root@db03 mysql]# sed -i 's#/usr/local/mysql#/service/mysql#g' /etc/init.d/mysqld /service/mysql/bin/mysqld_safe #修改配置文件 [root@db03 mysql]# vim /etc/my.cnf basedir = /service/mysql datadir = /service/mysql/data#再次启动 [root@db03 mysql]# /etc/init.d/mysqld start Starting MySQL.Logging to '/service/mysql/data/db03.err'.SUCCESS!
10）配置环境变量
[root@db03 scripts]# vim /etc/profile.d/mysql.sh export PATH=/service/mysql/bin:$PATH[root@db03 mysql]# source /etc/profile
11）确认启动
[root@m01 scripts]# ps -ef | grep mysql mysql 12886 1 2 03:10 ? 00:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf root 12921 10636 0 03:11 pts/1 00:00:00 grep --color=auto mysql [root@m01 scripts]# netstat -lntp tcp6 0 0 :::3306 :::* LISTEN 12886/mysqld
二、数据库基本操作
1.数据库设置密码
[root@db02 scripts]# mysqladmin -uroot password 123
2.使用密码登录
#1.正确的方式（不规范） [root@db02 scripts]# mysql -uroot -p123 [root@db02 scripts]# mysql -u root -p123#2.错误的方式 [root@db02 scripts]# mysql -uroot -p 123 #官网上说-p或者--password参数与密码之间不能有间隔或多于字符#3.正确且规范的方式 [root@db02 scripts]# mysql -u root -p Enter password:
3.查询用户
mysql> select user,host from mysql.user; +------+-----------+ | user | host | +------+-----------+ | root | 127.0.0.1 | | root | ::1 | | | db02 | | root | db02 | | | localhost | | root | localhost | +------+-----------+ 6 rows in set (0.00 sec)
4.删除用户（优化）
mysql> drop user root@'::1'; Query OK, 0 rows affected (0.00 sec)mysql> select user,host from user; +------+-----------+ | user | host | +------+-----------+ | root | 127.0.0.1 | | | db02 | | root | db02 | | | localhost | | root | localhost | +------+-----------+ 5 rows in set (0.00 sec)
三、企业误删除用户故障
1.不小心删除了mysql所有用户
mysql> delete from mysql.user where 1=1; Query OK, 5 rows affected (0.00 sec)#删除用户以后还是可以登陆，但是不要重启
2.解决方式一：
1）停止数据库
#抽根烟冷静一下 [root@db02 scripts]# systemctl stop mysqld
2）跳过授权表和网络启动
[root@db02 scripts]# mysqld_safe --skip-grant-tables --skip-networking &
3）插入新的用户
insert into mysql.user values ('localhost','root',PASSWORD('123'),
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'',
'',
'',
'',0,0,0,0,'mysql_native_password','','N');
4）重启启动数据库
[root@db02 scripts]# mysqladmin shutdown 200709 16:27:19 mysqld_safe mysqld from pid file /usr/local/mysql/data/db02.pid ended [1]+ Done mysqld_safe --skip-grant-tables --skip-networking [root@db02 scripts]# systemctl start mysqld
3.解决方式二：
1）停止数据库
#抽根烟冷静一下 [root@db02 scripts]# systemctl stop mysqld
2）跳过授权表和网络启动
[root@db02 scripts]# mysqld_safe --skip-grant-tables --skip-networking &
3）授权一个新用户
mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)mysql> grant all on *.* to root@'localhost' identified by '123' with grant option; Query OK, 0 rows affected (0.00 sec)