MySQL进阶管理

  1 二进制格式mysql安装
  2 本机在官网下载后利用xftp传输
  3 [root@localhost ~]# ls
  4 anaconda-ks.cfg                             :wq
  5 mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz
  6 创建MySQL组，不加目录，并设置无法登入
  7 [root@localhost ~]# groupadd -r -M -s /bin/nologin -g mysql mysql
  8 [root@localhost ~]# id mysql
  9 uid=27(mysql) gid=27(mysql) 组=27(mysql)
 10 解压
 11 [root@localhost ~]# tar xf mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz 
 12 [root@localhost ~]# ls
 13 anaconda-ks.cfg
 14 mysql-5.7.31-linux-glibc2.12-x86_64
 15 mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz
 16 :wq
 17 [root@localhost ~]# mv mysql-5.7.31-linux-glibc2.12-x86_64 /usr/local/
 18 [root@localhost ~]# ls /usr/local/
 19 bin    include  libexec                              share
 20 etc    lib      mysql-5.7.31-linux-glibc2.12-x86_64  src
 21 games  lib64    sbin
 22 映射到/usr/local/mysql下
 23 [root@localhost local]# ln -sv mysql-5.7.31-linux-glibc2.12-x86_64/ mysql
 24 ‘mysql‘ -> ‘mysql-5.7.31-linux-glibc2.12-x86_64/‘
 25 [root@localhost local]# ll
 26 总用量 0
 27 drwxr-xr-x. 2 root root    6 8月  12 2018 bin
 28 drwxr-xr-x. 2 root root    6 8月  12 2018 etc
 29 drwxr-xr-x. 2 root root    6 8月  12 2018 games
 30 drwxr-xr-x. 2 root root    6 8月  12 2018 include
 31 drwxr-xr-x. 2 root root    6 8月  12 2018 lib
 32 drwxr-xr-x. 2 root root    6 8月  12 2018 lib64
 33 drwxr-xr-x. 2 root root    6 8月  12 2018 libexec
 34 lrwxrwxrwx. 1 root root   36 12月 13 03:20 mysql -> mysql-5.7.31-linux-glibc2.12-x86_64/
 35 drwxr-xr-x. 9 7161 31415 129 6月   2 2020 mysql-5.7.31-linux-glibc2.12-x86_64
 36 drwxr-xr-x. 2 root root    6 8月  12 2018 sbin
 37 drwxr-xr-x. 5 root root   49 12月 16 2020 share
 38 drwxr-xr-x. 2 root root    6 8月  12 2018 src
 39 修改目录的属主与属组
 40 [root@localhost local]# chown -R mysql.mysql /usr/local/mysql
 41 [root@localhost local]# ll -d /usr/local/mysql
 42 lrwxrwxrwx. 1 mysql mysql 36 12月 13 03:20 /usr/local/mysql -> mysql-5.7.31-linux-glibc2.12-x86_64/
 43 添加环境变量
 44 [root@localhost local]# cd mysql
 45 [root@localhost mysql]# ls
 46 bin   include  LICENSE  README  support-files
 47 docs  lib      man      share
 48 [root@localhost mysql]# echo ‘export PATH=/usr/local/mysql/bin:$PATH‘ > /etc/profile.d/mysql.sh
 49 [root@localhost mysql]# . /etc/profile.d/mysql.sh 
 50 [root@localhost mysql]# echo $PATH
 51 /usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
 52 映射MySQL文件
 53 [root@localhost mysql]# ln -s /usr/local/mysql/include /usr/include/mysql
 54 添加帮助文档
 55 [root@localhost mysql]# vim /etc/man_db.conf
 56 MANDATORY_MANPATH                       /usr/local/mysql/man
 57 添加lib库文件地址
 58  
 59  
 60 [root@localhost mysql]# vim /etc/ld.so.conf.d/mysql.cof
 61 [root@localhost mysql]# ldconfig 
 62 创建数据存放目录，并修改属主与属组
 63 [root@localhost mysql]# mkdir /opt/data
 64 [root@localhost mysql]# chown -R mysql.mysql /opt/data/
 65 [root@localhost mysql]# ll /opt/
 66 总用量 0
 67 drwxr-xr-x. 2 mysql mysql   6 12月 13 03:28 data
 68 初始化配置，并获取临时密码
 69 [root@localhost ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/
 70 2020-12-12T22:54:25.149163Z 1 [Note] A temporary password is generated for root@localhost: )0c4gRhBdzi!
 71 将密码保存
 72 [root@localhost ~]# echo "0c4gRhBdzi!" > ww
 73 生成配置文件
 74 [root@localhost ~]# vim /etc/my.cnf
 75 [mysqld]
 76 basedir = /usr/local/mysql
 77 datadir = /opt/data
 78 socket = /tmp/mysql.sock
 79 port = 3306
 80 pid-file = /opt/data/mysql.pid
 81 user = mysql
 82 skip-name-resolve
 83 配置服务启动脚本
 84 [root@localhost support-files]# cp mysql.server /etc/init.d/mysqld
 85 [root@localhost support-files]# cd
 86 [root@localhost ~]# ll /etc/init.d/mysqld 
 87 -rwxr-xr-x. 1 root root 10576 12月 13 07:03 /etc/init.d/mysqld
 88 [root@localhost ~]# vim /etc/init.d/mysqld
 89 basedir=/usr/local/mysql
 90 datadir=/opt/data
 91 启动进程
 92 [root@localhost ~]# service mysqld start 
 93 Starting MySQL. SUCCESS! 
 94  [root@localhost ~]# ss -antl
 95 State Recv-Q Send-Q  Local Address:Port Peer Address:Port 
 96 LISTEN0      128           0.0.0.0:22        0.0.0.0:*    
 97 LISTEN0      128              [::]:22           [::]:*    
 98 LISTEN0      80                  *:3306            *:*   
 99 进入mysql，报错缺少依赖包
100 查看依赖包由那个包提供
101 [root@localhost ~]# yum whatprovides libncurses.so.5
102 Updating Subscription Management repositories.
103 Unable to read consumer identity
104 This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
105 ncurses-compat-libs-6.1-7.20180224.el8.i686 : Ncurses compatibility libraries
106 仓库        ：BaseOS
107 匹配来源：
108 提供    : libncurses.so.5
109 安装mysql依赖包ncurses-compat-libs-6.1-7.20180224.el8.i686
110 [root@localhost ~]# yum -y install ncurses-compat-libs-6.1-7.20180224.el8.i686 
111 Updating Subscription Management repositories.
112 Unable to read consumer identity
113 This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
114 appstream                                                                                674 kB/s | 3.2 kB     00:00    
115 baseos                                                                                   1.4 MB/s | 2.7 kB     00:00    
116 依赖关系解决。
117 =========================================================================================================================
118  软件包                             架构                版本                                 仓库                   大小
119 =========================================================================================================================
120 Installing:
121  ncurses-compat-libs                i686                6.1-7.20180224.el8                   BaseOS                350 k
122 安装依赖关系:
123  glibc                              i686                2.28-42.el8                          BaseOS                3.6 M
124  libgcc                             i686                8.2.1-3.5.el8                        BaseOS                 84 k
125  libstdc++                          i686                8.2.1-3.5.el8                        BaseOS                485 k
126  
127  
128 已安装:
129   ncurses-compat-libs-6.1-7.20180224.el8.i686          glibc-2.28-42.el8.i686          libgcc-8.2.1-3.5.el8.i686         
130   libstdc++-8.2.1-3.5.el8.i686                        
131 完毕！
132    修改密码
133 [root@localhost ~]# mysql -uroot -p‘)0c4gRhBdzi!‘
134 mysql> set password = password(‘123456‘);
135 Query OK, 0 rows affected, 1 warning (0.01 sec)
136 查看MySQL运行进行，以及停止MySQL
137 [root@localhost ~]# service mysqld status 
138  SUCCESS! MySQL running (3632)
139 [root@localhost ~]# service mysqld stop
140 Shutting down MySQL.. SUCCESS! 
141 [root@localhost ~]# ss -antl
142 State Recv-Q Send-Q  Local Address:Port Peer Address:Port 
143 LISTEN0      128           0.0.0.0:22        0.0.0.0:*    
144 LISTEN0      128              [::]:22           [::]:*
145 查看开机自动启动项
146 [root@localhost ~]# systemctl list-dependencies 
147 设置开机自动启动
148 [root@localhost ~]# chkconfig --add mysqld 
149 [root@localhost ~]# chkconfig mysqld on
150 [root@localhost ~]# chkconfig --list 
151 mysqld          0:关 1:关 2:开 3:开 4:开 5:开 6:关
152 重启，查看mysql是否启动
153 [root@localhost ~]# reboot
154 [root@localhost ~]# ss -antl
155 State Recv-Q Send-Q  Local Address:Port Peer Address:Port 
156 LISTEN0      128           0.0.0.0:22        0.0.0.0:*    
157 LISTEN0      128              [::]:22           [::]:*    
158 LISTEN0      80                  *:3306            *:* 
159 设置配置文件
160 [root@localhost ~]# vim .my.cnf
161 [client]
162 user=root
163 password=123456
164 查看数据库：配置。my.cnf文件后可在root中不使用密码查看数据库内容
165 [root@localhost ~]# mysql -e ‘show databases‘
166 +--------------------+
167 | Database           |
168 +--------------------+
169 | information_schema |
170 | mysql              |
171 | performance_schema |
172 | sys                |
173 +--------------------+
174 修改MySQL端口为3307
175 [root@localhost ~]# vim /etc/my.cnf
176 [mysqld]
177 basedir = /usr/local/mysql
178 datadir = /opt/data
179 socket = /tmp/mysql.sock
180 port = 3307
181 pid-file = /opt/data/mysql.pid
182 user = mysql
183 skip-name-resolve
184 [root@localhost ~]# service mysqld restart 
185 Shutting down MySQL.. SUCCESS! 
186 Starting MySQL. SUCCESS! 
187 [root@localhost ~]# ss -antl
188 State Recv-Q Send-Q  Local Address:Port Peer Address:Port 
189 LISTEN0      128           0.0.0.0:22        0.0.0.0:*    
190 LISTEN0      128              [::]:22           [::]:*    
191 LISTEN0      80                  *:3307            *:* 
192 数据库密码破解
193 第一步修改配置文件
194 第二步重启
195 第三步可直接登入数据库修改密码
196  第四步修改配置文件，删除skip-grant-tables
197 第五步重启服务
198 第六步验证密码是否修改成功
199 [root@localhost ~]# vim /etc/my.cnf
200 加入：skip-grant-tables（跳过授权表）
201 [root@localhost ~]# service mysqld restart 
202 Shutting down MySQL.. SUCCESS! 
203 Starting MySQL. SUCCESS! 
204 [root@localhost ~]# mysql
205 进入数据表
206 mysql> use mysql;
207 mysql> show tables;
208 mysql> select * from user\G
209 *************************** 1. row ***************************
210                   Host: localhost
211                   User: root
212            
213  authentication_string: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
214  mysql> update user set authentication_string =password(‘wangming123!‘) where User=‘root‘ and Host=‘localhost‘;（修改密码）
215 Query OK, 1 row affected, 1 warning (0.01 sec)
216 Rows matched: 1  Changed: 1  Warnings: 1
217 删除文件配置中的skip-grant-tables
218 [root@localhost ~]# vim /etc/my.cnf
219  skip-grant-tables（删除）
220 重启服务
221 [root@localhost ~]# service mysqld restart 
222 Shutting down MySQL.. SUCCESS! 
223 Starting MySQL.. SUCCESS! 
224 重新登入MySQL验证密码是否更改
225 [root@localhost ~]# mysql -uroot -p‘wangming123!‘
226 mysql: [Warning] Using a password on the command line interface can be insecure.（登入成功）
227 创建数据表，并创建数据
228 mysql> create database school ;
229 Query OK, 1 row affected (0.01 sec)
230 mysql> use school;
231 Database changed
232 mysql> create table student(id int not null primary key auto_increment,name varchar(50),age tinyint);
233 Query OK, 0 rows affected (0.07 sec)
234 mysql> insert student(name,age) values(‘tom‘,20),(‘jerry‘,155),(‘zhangshan‘,23);
235 Query OK, 3 rows affected (0.05 sec)
236 Records: 3  Duplicates: 0  Warnings: 0
237 mysql> select * from student;
238 +----+-----------+------+
239 | id | name      | age  |
240 +----+-----------+------+
241 |  1 | tom       |   20 |
242 |  2 | jerry     |   15 |
243 |  3 | zhangshan |   23 |
244 +----+-----------+------+
245 3 rows in set (0.00 sec)
246 备份数据将数据写入all.sql中
247 [root@localhost ~]# mysqldump -uroot -pwangming123! --all-databases> all.sql
248 mysqldump: [Warning] Using a password on the command line interface can be insecure.
249 [root@localhost ~]# ls
250 all.sql                                     :wq
251 anaconda-ks.cfg                             ww
252 mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz
253 删除school数据库
254 [root@localhost ~]# mysql -uroot -pwangming123! -e ‘drop database school;‘
255 mysql: [Warning] Using a password on the command line interface can be insecure.
256 [root@localhost ~]# mysql -uroot -pwangming123! -e ‘show databases;‘
257 mysql: [Warning] Using a password on the command line interface can be insecure.
258 +--------------------+
259 | Database           |
260 +--------------------+
261 | information_schema |
262 | mysql              |
263 | performance_schema |
264 | sys                |
265 +--------------------+
266 恢复school数据库
267 [root@localhost ~]# mysql -uroot -pwangming123! < all.sql 
268 mysql: [Warning] Using a password on the command line interface can be insecure.
269 [root@localhost ~]# mysql -uroot -pwangming123! -e ‘show databases;‘
270 mysql: [Warning] Using a password on the command line interface can be insecure.
271 +--------------------+
272 | Database           |
273 +--------------------+
274 | information_schema |
275 | mysql              |
276 | performance_schema |
277 | school             |
278 | sys                |
279 +--------------------+
280 [root@localhost ~]# mysql -uroot -pwangming123! -e ‘select * from school.student;‘
281 mysql: [Warning] Using a password on the command line interface can be insecure.
282 +----+-----------+------+
283 | id | name      | age  |
284 +----+-----------+------+
285 |  1 | tom       |   20 |
286 |  2 | jerry     |   15 |
287 |  3 | zhangshan |   23 |
288 +----+-----------+------+