SQL的综合知识

SQL的综合知识

设置开机自启

可以在/etc/rc.local中加入

[root@localhost /]# echo "/my_mysql/3306/mysql_3306 start" >> /etc/rc.local 
[root@localhost /]# echo "/my_mysql/3307/mysql_3307 start" >> /etc/rc.local 
[root@localhost /]# tail -n 2 /etc/rc.local 
/my_mysql/3306/mysql_3306 start
/my_mysql/3307/mysql_3307 start

数据库分为服务端和客户端

• 服务端我们已经搭建完毕
• 客户端可以有很多
  • 可以通过连接工具Navicat。。等等
  • 可以通过服务本地mysql客户端mysql -uroot这样的形式登入
  • 可以通过从别的设备客户端远程
  • 可以通过本地的套接字socket进行登入

演示本地服务器通过socket登入

mysql -S 套接字目录 -p密码

eg：mysql -S /my_mysql/3306/mysql.sock -p

本地如果启动了mysql会生成对应的socket套接字与内存实时交互

如果杀了进程之后就不会有了

• 只要启动就会有套接字&pid文件

[root@localhost 3306]# netstat -antpul |grep mysql
tcp6       0      0 :::3306                 :::*                    LISTEN      3795/mysqld         
tcp6       0      0 :::3307                 :::*                    LISTEN      2974/mysqld         
[root@localhost /]# find /my_mysql/ -name *.pid
/my_mysql/3306/mysqld_3306.pid
/my_mysql/3307/mysqld_3307.pid
[root@localhost 3306]# find /my_mysql/ -name *.sock
/my_mysql/3306/mysql.sock
/my_mysql/3307/mysql.sock

#pid进程号和进程是对应的
[root@localhost 3306]# cat mysqld_3306.pid 
3795
[root@localhost 3307]# cat mysqld_3307.pid
2974
[root@localhost 3306]# ps -ef |grep mysqld
root      2789     1  0 15:05 pts/1    00:00:00 /bin/sh /application/mysql-5.6.50-linux-glibc2.12-x86_64/bin/mysqld_safe --defaults-file=/my_mysql/3307/my.cnf --pid-file=/my_mysql/3307/mysqld_3307.pid
mysql     2974  2789  0 15:05 pts/1    00:00:00 /application/mysql-5.6.50-linux-glibc2.12-x86_64/bin/mysqld --defaults-file=/my_mysql/3307/my.cnf --basedir=/application/mysql-5.6.50-linux-glibc2.12-x86_64/ --datadir=/my_mysql/3307/data --plugin-dir=/application/mysql-5.6.50-linux-glibc2.12-x86_64//lib/plugin --user=mysql --log-error=/my_mysql/3307/mysql_3307_error.log --pid-file=/my_mysql/3307/mysqld_3307.pid --socket=/my_mysql/3307/mysql.sock --port=3307
root      3610     1  0 15:12 pts/1    00:00:00 /bin/sh /application/mysql-5.6.50-linux-glibc2.12-x86_64/bin/mysqld_safe --defaults-file=/my_mysql/3306/my.cnf --pid-file=/my_mysql/3306/mysqld_3306.pid
mysql     3795  3610  0 15:12 pts/1    00:00:00 /application/mysql-5.6.50-linux-glibc2.12-x86_64/bin/mysqld --defaults-file=/my_mysql/3306/my.cnf --basedir=/application/mysql-5.6.50-linux-glibc2.12-x86_64/ --datadir=/my_mysql/3306/data --plugin-dir=/application/mysql-5.6.50-linux-glibc2.12-x86_64//lib/plugin --user=mysql --log-error=/my_mysql/3306/mysql_3306_error.log --pid-file=/my_mysql/3306/mysqld_3306.pid --socket=/my_mysql/3306/mysql.sock --port=3306

-S 指定套接字的目录，如果有密码的话，需要加-p
[root@localhost 3306]# mysql -S /my_mysql/3306/mysql.sock -p

[root@localhost 3306]# mysql -S /my_mysql/3306/mysql.sock 
Welcome to the MySQL monitor.  Commands end with ; or /g.
Your MySQL connection id is 1
Server version: 5.6.50-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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> 

演示端口+ip登入

mysql -u用户名 -p密码 -h登入ip -P端口

[root@localhost /]# mysql -uroot -p -h127.0.0.1 -P
mysql: option '-P' requires an argument
[root@localhost /]# mysql -uroot -p -h127.0.0.1 -P3306
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or /g.
Your MySQL connection id is 2
Server version: 5.6.50-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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> 

设置密码

使用mysqladmin该密码，增加密码

还有报错

mysqladmin -uroot -p password直接报错

[root@localhost /]# mysqladmin -uroot -p password
Enter password: 
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)'
Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists!
[root@localhost /]# 

多实例需要通过socket进行改

密码123456

[root@localhost /]# mysqladmin -S /my_mysql/3306/mysql.sock -p password
Enter password: 
New password: 
Confirm new password: 
[root@localhost /]# 

[root@localhost /]# mysqladmin -S /my_mysql/3307/mysql.sock -p password
Enter password: 
New password: 
Confirm new password: 
[root@localhost /]# 

剩下的就是远程登入了

通过mysql -u用户名 -p密码 -h登入ip -P端口

需要知道的是远程登入需要授权