mysql知识学习
********mysql******
yum search db
yum install mariadb-server.x86_64 -y
systemctl restart mariadb
mysql #进入数据库软件
SHOW DATAbaseS ;#呈现数据库
USE mysql;#使用mysql库
SHOW TABLES;#呈现表
SELECT * FORM user;呈现表的内容
SELECT Host FROM user;呈现某一字段
SELECT Host FROM mysql.user ;
CREATE DATAbase westos;创建数据库westos
USE westos;进入数据库
CREATE TABLE linux( #创建一个表单linux
username varchar(15) not null;
passwd varchar(15) not null
);
ALTER TABLE linux ADD age varchar(4);改变表单的结构
ALTER TABLE linux DROP age;
ALTER TABLE linux ADD age varchar(5) AFTER username;
DESC linux;查看表单的数据结构
INSERT INTO linux VALUES ('user1','passwd1');插入值
UPDATE linux SET passwd='1' WHERE username='user1';更新值
DELETE FROM linux WHERE username='user1';删除数据
DROP TABLE linux;删除表
DROP DATAbase westos;删除数据库
quit 退出
关闭端口，避免将数据库暴露在网络中
netstat -antlpe |grep mysqld
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 27 105425 3124/mysqld
vim /etc/my.cnf
skip-networking=1
systemctl restart mariadb
netstat -antlepe |grep mysqld
第一次安装对mysql进行设置
mysql_secure_installation
mysql -uroot -predhat
mysql -uroot -p #回车后输入密码
数据库备份和恢复
mysqldump -uroot -predhat westos > /mnt/westos.sql
mysql -uroot -predhat -e "DROP DATAbase westos;"
mysql -uroot -predhat -e "SHOW DATAbaseS;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
mysql -uroot -predhat -e "CREATE DATAbase westos;"
mysql -uroot -predhat westos < /mnt/westos.sql
mysql -uroot -predhat -e "SHOW DATAbaseS;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| westos |
+--------------------+
创建用户并授予权力
CREATE USER westos@localhost identified by "westos";#创建用户且密码为westos
mysql -uwestos -pwestos ;
SHOW GRANTS FOR westos@localhost;
GRANT SELECT ON *.* TO westos@localhost;
REVOKE SELECT ON *.* FROM westos@localhost;
DROP USER westos@localhost ;
忘记密码后更改密码
mysqladmin -uroot -pwestos password lee
systemctl stop mariadb
ps aux | grep mysql
root 7947 0.0 0.4 235704 4020 pts/2 S+ 09:20 0:00 mysql -uwestos -p
root 8125 0.0 0.0 112640 936 pts/1 R+ 09:27 0:00 grep --color=auto mysql
kill -9 7947 #终止所有有关mysql的进程
mysqld_safe --skip-grant-table &
mysql 进入数据库软件
USE mysql ;
SELECT * FROM user ;
UPDATE user SET Password="westos" where User="root" ;#密码为明文
UPDATE user SET Password=password("westos") where User="root" ;#密码为暗文
exit
ps aux |grep mysql
root 8170 0.0 0.1 113248 1568 pts/1 S 09:28 0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-table
mysql 8328 0.0 8.9 860772 88672 pts/1 Sl 09:28 0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --skip-grant-table --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock
root 8527 0.0 0.0 112640 936 pts/1 S+ 09:35 0:00 grep --color=auto mysql
kill -9 8170#打断所有与mysql 相关的进程只剩下
root 8579 0.0 0.0 112640 936 pts/1 R+ 09:37 0:00 grep --color=auto mysql
systemctl restart mariadb
mysql -uroot -pwestos
mysql 有图形界面mysqladmin