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 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下
cd /usr/local 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 +----+-----------+------+
1 差异备份 2 开启MySQL服务器的二进制日志功能 3 [root@localhost ~]# vim /etc/my.cnf 4 #添加内容 5 server-id=1 #设置服务器标识符 6 log-bin=mysql_bin #开启二进制日志功能 7 重启服务 8 [root@localhost ~]# service mysqld restart 9 Shutting down MySQL.... SUCCESS! 10 Starting MySQL.. SUCCESS! 11 创建数据表 12 mysql> insert into runtime(id,name,age) values (1,'tom',10),,(2,'jerry',30)； 13 Query OK, 2 rows affected (0.01 sec) 14 Records: 2 Duplicates: 0 Warnings: 0 15 mysql> insert into wangming (name,age) values ('tom',20),('jerry',23),('wangqing',25),('sean',28),('zhangshan',26),('zhangshan',20),('lisi',null),('chenshuo',10),('wangwu',3),('qiuyi',15),('qiuxiaotian',20); 16 Query OK, 11 rows affected (0.03 sec) 17 Records: 11 Duplicates: 0 Warnings: 0 18 查看wangming数据库中的数据表 19 mysql> show tables from wangming; 20 +--------------------+ 21 | Tables_in_wangming | 22 +--------------------+ 23 | runtime | 24 | wangming | 25 +--------------------+ 26 2 rows in set (0.01 sec) 27 查看数据表中的内容 28 mysql> select * from wangming.runtime; 29 +----+--------+------+ 30 | id | name | age | 31 +----+--------+------+ 32 | 1 | tom | 10 | 33 | 2 | jerry | 30 | 34 +----+--------+------+ 35 36 37 差异备份前先完全备份 38 完全备份成功 39 [root@localhost ~]# mysqldump -uroot -pwangming123! --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-201902211406.sql 40 [root@localhost ~]# ls 41 all-201902211406.sql 42 添加新内容 43 mysql> use wangming; 44 Reading table information for completion of table and column names 45 You can turn off this feature to get a quicker startup with -A 46 Database changed 47 mysql> insert into runtime values(3,'wangwu',25);; 48 Query OK, 2 rows affected (0.01 sec) 49 Records: 2 Duplicates: 0 Warnings: 0 50 mysql> select * from runtime; 51 +----+--------+------+ 52 | id | name | age | 53 +----+--------+------+ 54 | 1 | tom | 10 | 55 | 2 | jerry | 30 | 56 | 3 | wangwu | 25 | 57 +----+--------+------+ 58 3 rows in set (0.00 sec) 59 修改内容 60 mysql> update runtime set age = 50 where id = 3 ; 61 Query OK, 1 row affected (0.01 sec) 62 Rows matched: 1 Changed: 1 Warnings: 0 63 mysql> select * from runtime; 64 +----+--------+------+ 65 | id | name | age | 66 +----+--------+------+ 67 | 1 | tom | 10 | 68 | 2 | jerry | 30 | 69 | 3 | wangwu | 50 | 70 +----+--------+------+ 71 3 rows in set (0.02 sec) 72 删除数据库 73 [root@localhost ~]# mysql -e 'drop database wangming;' 74 [root@localhost ~]# mysql -e 'show databases;' 75 +--------------------+ 76 | Database | 77 +--------------------+ 78 | information_schema | 79 | mysql | 80 | performance_schema | 81 +--------------------+ 82 刷新二进制日志 83 [root@localhost data]# mysqladmin -uroot -pwangming123! flush-logs 84 mysqladmin: [Warning] Using a password on the command line interface can be insecure. 85 [root@localhost data]# ll 86 -rw-r-----. 1 mysql mysql 949 12月 14 07:35 mysql_bin.000003 87 -rw-r-----. 1 mysql mysql 177 12月 14 07:35 mysql_bin.000004 88 -rw-r-----. 1 mysql mysql 201 12月 15 02:15 mysql_bin.000005 89 -rw-r-----. 1 mysql mysql 154 12月 15 02:15 mysql_bin.000006 90 恢复备份 91 [root@localhost ~]# ls 92 all-201902211406.sql anaconda-ks.cfg 93 [root@localhost ~]# mysql < all-201902211406.sql 94 [root@localhost ~]# mysql -e 'show databases;' 95 +--------------------+ 96 | Database | 97 +--------------------+ 98 | information_schema | 99 | mysql | 100 | performance_schema | 101 | wangming | 102 查看数据库中的内容 103 [root@localhost ~]# mysql -e 'show tables from wangming;' 104 +--------------------+ 105 | Tables_in_wangming | 106 +--------------------+ 107 | runtime | 108 | wangming | 109 +--------------------+ 110 查看数据表中的内容 111 [root@localhost ~]# mysql -e 'select * from wangming.runtime;' 112 +----+--------+------+ 113 | id | name | age | 114 +----+--------+------+ 115 | 1 | tom | 10 | 116 | 2 | jerry | 30 | 117 +----+--------+------+ 118 [root@localhost ~]# mysql -e 'select * from wangming.wangming;' 119 +----+-------------+------+ 120 | id | name | age | 121 +----+-------------+------+ 122 | 1 | tom | 20 | 123 | 2 | jerry | 23 | 124 | 3 | wangqing | 25 | 125 | 4 | sean | 28 | 126 | 5 | zhangshan | 26 | 127 | 7 | lisi | 50 | 128 | 8 | chenshuo | 10 | 129 | 9 | wangwu | 100 | 130 | 10 | qiuyi | 15 | 131 | 11 | qiuxiaotian | 20 | 132 检查误删除的数据库的位置在什么地方 133 mysql> mysql> show binlog events in 'mysql_bin.000005'; 134 +------------------+-----+----------------+-----------+-------------+---------------------------------------+ 135 | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | 136 +------------------+-----+----------------+-----------+-------------+---------------------------------------+ 137 | mysql_bin.000005 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.31-log, Binlog ver: 4 | 138 | mysql_bin.000005 | 123 | Previous_gtids | 1 | 154 | | 139 | mysql_bin.000005 | 154 | Rotate | 1 | 201 | mysql_bin.000006;pos=4 | 140 +------------------+-----+----------------+-----------+-------------+---------------------------------------+ 141 3 rows in set (0.00 sec) 142 恢复差异备份 143 [root@localhost ~]# mysql -e 'select * from wangming.runtime;' 144 +----+--------+------+ 145 | id | name | age | 146 +----+--------+------+ 147 | 1 | tom | 10 | 148 | 2 | jerry | 30 | 149 | 3 | wangwu | 20| 150 +----+--------+------+
浙公网安备 33010602011771号