1.mysql自带的客户端程序(/service/mysql/bin)
mysql
mysqladmin
mysqldump
2.mysqld一个二进制程序,后台的守护进程
单进程
多线程
2)MySQL的两种连接方式
1.TCP/IP的连接方式
2.套接字连接方式,socket连接
#查看连接方式
mysql> status;
--------------
Connection: Localhost via UNIX socket
3.举例:
3.1.TCP/IP连接
mysql -uroot -p -h127.0.0.1
mysql -uroot -p -h127.0.0.1 -S /tmp/mysql.sock
3.2.socket连接
mysql -uroot -p -hlocalhost
mysql -uroot -p123(默认连接方式,socket)
4.注意:
4.1.因为使用TCP/IP连接,需要建立三次握手
4.2.不一定-h都是tcp,-hlocalhost是socket连接
3)MySQL服务构成
2、mysql体系基本管理
1)MySQL管理
#启动MySQL[root@hzl ~]# ps aux |grep mysqld |grep -v grep #查看进程,mysqld_safe为启动mysql的脚本文件,内部调用mysqld命令
mysql 3329 0.0 0.0 113252 1592 ? Ss 16:19 0:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
mysql 3488 0.0 2.3 839276 90380 ? Sl 16:19 0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock
[root@hzl ~]# netstat -an |grep 3306 #查看端口
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN
[root@egon ~]# ll -d /var/lib/mysql #权限不对,启动不成功,注意user和group
drwxr-xr-x 5 mysql mysql 4096 Jul 20 16:28 /var/lib/mysql
#小插曲
安装完mysql 之后,登陆以后,不管运行任何命令,总是提示这个
mac mysql error You must reset your password using ALTER USER statement before executing this statement.
#解决方法:
step 1: SET PASSWORD = PASSWORD('your new password');
step 2: ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
step 3: flush privileges;
2)密码设置
#初始状态下,管理员root,密码为空,默认只允许从本机登录localhost[root@hzl ~]# mysql #默认密码为空,可直接输入mysql登录
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.46 Source distribution
.......
....
Type 'help;' or '\h'for help. Type '\c' to clear the current input statement.
mysql>#设置MySQL密码[root@hzl ~]# mysqladmin -uroot password "1" #设置初始密码 [root@hzl ~]# mysqladmin -uroot -p"1" password "123" #修改mysql密码,必须输入原密码才能设置新密码#登录命令格式:[root@hzl ~]# mysql -h192.168.15.52 -uroot -p123 #远程登录[root@hzl ~]# mysql -uroot -p123 #本地登录方式[root@hzl ~]# mysql #以root用户登录本机,首次登录密码为空,无需输入密码
3)忘记密码(重置密码)
#方式一:(不建议使用,简单粗暴)[root@hzl ~]# rm -rf /var/lib/mysql/mysql #删除所有授权信息全部丢失!!![root@hzl ~]# systemctl restart mysql #重新启动[root@hzl ~]# mysql #方式二:(修改配置文件,启动时,跳过授权库)[root@hzl ~]# vim /etc/my.cnf #mysql主配置文件[mysqld]
skip-grant-table #添加此参数,表示跳过密码认证[root@hzl ~]# systemctl restart mysql[root@hzl ~]# mysql #登录时,直接可以登录
MariaDB [(none)]> update mysql.user set password=password("123") where user="root" and host="localhost";#进入MySQL,使用此命令进行重置密码
mysql> flush privileges;#刷新权限
mysql> q\
[root@hzl ~]# #打开/etc/my.cnf去掉skip-grant-table,然后重启[root@hzl ~]# systemctl restart mysql[root@hzl ~]# mysql -u root -p123 #以新密码登录
1)mysqladmin -uroot -p123 password '1'
2)update mysql.user set password=password('123') where user='root' and host='localhost';
3)set password=password('1');#修改当前用户的密码
4)grant all on *.* to 'root'@'localhost' identified by '123';
四、mysql连接管理
1、mysql自带的连接命令 mysql
mysql
#常见的特定于客户机的连接选项:
-u: 指定用户 mysql -uroot
-p: 指定密码 mysql -uroot -p567
-h: 指定主机域 mysql -uroot -p567 -h127.0.0.1
-P: 指定端口 mysql -uroot -p567 -h127.0.0.1 -P3307
-S: 指定socket文件 mysql -uroot -p567 -S /tmp/mysql.sock
-e: 指定SQL语句(库外执行SQL语句) mysql -uroot -p567 -e "show databases;"#--protocol: 指定连接方式 mysql --protocol=TCP --protocol=socket
[root@db01 scripts]# mysql -uroot -p3307 -S /data/3307/mysql.sock -e "show variables like 'server_id';"
Warning: Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 7 |
+---------------+-------+
[root@db01 scripts]# mysql -uroot -p3308 -S /data/3308/mysql.sock -e "show variables like 'server_id';"
Warning: Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 8 |
+---------------+-------+
[root@db01 scripts]# mysql -uroot -p3309 -S /data/3309/mysql.sock -e "show variables like 'server_id';"
Warning: Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 9 |
+---------------+-------+
10.连接多实例小技巧
[root@db01 scripts]# vim /usr/bin/mysql3309
mysql -uroot -p3309 -S /data/3309/mysql.sock
[root@db01 scripts]# vim /usr/bin/mysql3308
mysql -uroot -p3308 -S /data/3308/mysql.sock
[root@db01 scripts]# vim /usr/bin/mysql3307
mysql -uroot -p3307 -S /data/3307/mysql.sock
[root@db01 scripts]# chmod +x /usr/bin/mysql*
11、基础配置文件(优化字符设)
[root@db01 system]# cat /etc/my.cnf[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
#skip-grant-table #跳过密码使用# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.# If you need to run mysqld under a different user or group,# customize your systemd unit file for mariadb according to the# instructions in http://fedoraproject.org/wiki/Systemd[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
default-character-set=utf8mb4
# include all files from the config directory!includedir /etc/my.cnf.d
#[client]#default-character-set=utf8mb4