MySQL学习记录

简单记录了一下自己学MySQL的过程，求收藏求点赞！！！

主要学习这几部分：部署及服务管理、用户及权限、SQL、性能调优、数据备份、集群搭建。

 

最简单的入门资料：

建议使用w3c的教程。

https://www.runoob.com/mysql/mysql-tutorial.html

https://www.w3cschool.cn/mysql/

一份比较深入的学习教程：

http://www.notedeep.com/note/38/page/327

 

部署及服务管理

mysql部署：

https://www.w3cschool.cn/mysql/mysql-install.html

查看目前已安装的mysql相关包，并进行删除。下载mysql的rpm包，数据库服务器直接安装server即可：

[root@localhost opt]# rpm -ivh mysql-community-release-el7-5.noarch.rpm
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-release-el7-5    ################################# [100%]

并执行：

yum update
yum install mysql-server

进行权限设置：

[root@localhost opt]# chown mysql:mysql -R /var/lib/mysql

验证mysql安装：

[root@localhost opt]# mysqladmin --version
mysqladmin  Ver 8.42 Distrib 5.6.51, for Linux on x86_64

启动mysql服务并确保mysql服务是开机启动的

[root@localhost opt]# systemctl status mysql
● mysqld.service - MySQL Community Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Tue 2021-03-02 15:00:00 CST; 2min 31s ago
  Process: 76496 ExecStartPost=/usr/bin/mysql-systemd-start post (code=exited, status=0/SUCCESS)
  Process: 76478 ExecStartPre=/usr/bin/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 76495 (mysqld_safe)
    Tasks: 23
   CGroup: /system.slice/mysqld.service
           ├─76495 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
           └─76661 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mysqld.log ...

Mar 02 14:59:59 localhost systemd[1]: Starting MySQL Community Server...
Mar 02 14:59:59 localhost mysqld_safe[76495]: 210302 14:59:59 mysqld_safe Logging to '/var/log/mysqld.log'.
Mar 02 14:59:59 localhost mysqld_safe[76495]: 210302 14:59:59 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
Mar 02 15:00:00 localhost systemd[1]: Started MySQL Community Server.

查看mysql服务是否启动：

[root@localhost opt]# ps -ef | grep mysqld
mysql     76495      1  0 14:59 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
mysql     76661  76495  0 14:59 ?        00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
root      76815  43499  0 15:04 pts/1    00:00:00 grep --color=auto mysqld

使用systemctl管理mysql服务即可。

Mysql安装成功后，默认的root用户密码为空，使用以下命令来创建root用户的密码：

[root@localhost opt]# mysqladmin -u root password "password"
Warning: Using a password on the command line interface can be insecure.

通过以下命令来连接到Mysql服务器：

[root@localhost opt]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.51 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

 

用户及权限

创建用户，并使用Navicat连接数据库：

https://mp.csdn.net/console/editor/html/114264479

mysql修改密码方法：

https://www.w3cschool.cn/mysql/mysql-wauz2owl.html

其他参考资料：

https://blog.csdn.net/weixin_33835103/article/details/91711205?utm_medium=distribute.pc_relevant.none-task-blog-baidujs_title-2&spm=1001.2101.3001.4242

https://blog.csdn.net/weixin_51622156/article/details