linux系统下mysql二进制和编译安装及企业误删除(2)

一、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/bin/mysqld --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）确认启动

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)