LAMP第四部分mysql操作
课程大纲：1. 忘记root密码
编辑mysql主配置文件 my.cnf 在字段下添加参数skip-grant,重启数据库服务,这样就可以进入数据库不用授权了 mysql -uroot ,修改相应用户密码 use mysql; update user set password=password('your password') where user='root';flush privileges;最后修改/etc/my.cnf 去掉 skip-grant , 重启mysql服务
2. skip-innodb 我们可以增加这个参数不使用innodb引擎。
3. 配置慢查询日志
#log_slow_queries = /path/to/slow_queries
#long_query_time = 1
4. mysql常用操作
mysql远程备份和恢复
远程登录mysql
116.211.105.59先授权
mysql -uroot -paming
mysql> grant allon*.* to 'root'@'58.53.94.11' identified by '123aaa';
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql>select database();
mysql>select database();
+------------+
| database() |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)
mysql> grant all on discuz.* to 'user1'@'192.168.10.*' identified by '123aaa';
discuz
user1
123aaa
mysql> flushprivileges;
mysql> showprocesslist;
58.53.94.11登录
[root@xiantao-11 ~]# mysql-uroot-h116.211.105.59 -P3306 -p123aaa
[root@wuhan-67 /]# mysqldump --host 111.47.123.72-uroot-paminglinux discuz > /data/discuz.sql
mysqldump: Got error: 1130: Host '111.47.123.67' is not allowed to connect to this MySQL server when trying to connect
[root@wuhan-67 /]# mysqldump --host 111.47.123.72-uroot-paminglinux discuz > /data/discuz.sql
[root@wuhan-67 /]# cd /data
[root@wuhan-67 data]# ll
total 2308
-rw-r--r-- 1 root root 2361322 Aug3 22:27 discuz.sql
查看都有哪些库show databases;
查看某个库的表 use db; show tables;
查看表的字段 desc tb;
查看建表语句 show create table tb;
当前是哪个用户select user();
当前库 select database();
创建库 create database db1;
创建表 create table t1 (`id` int(4), `name` char(40));
查看数据库版本 select version();
查看mysql状态 show status;
修改mysql参数 show variables like 'max_connect%'; set global max_connect_errors = 1000;
查看mysql队列 show processlist;
创建普通用户并授权 grant all on *.* to user1 identified by '123456';
grant all on db1.* to 'user2'@'10.0.2.100' identified by '111222';
grant all on db1.* to 'user3'@'%' identified by '231222';insert into tb1 (id,name) values(1,'aming');
更改密码 UPDATE mysql.user SET password=PASSWORD("newpwd") WHERE user='username' ;
查询 select count(*) from mysql.user; select * from mysql.db; select * from mysql.db where host like '10.0.%';
插入 update db1.t1 set name='aaa' where id=1;
清空表 truncate table db1.t1;
删除表 drop table db1.t1;
删除数据库 drop database db1;
修复表 repair table tb1 ;
5. mysql备份与恢复
[root@wuhan-72 ~]# find/-namemysqldump
/usr/local/mysql/bin/mysqldump
[root@localhost mysql]# mysqldump -uroot -pamingdiscuz > /data/discuz.sql/1.sql
linuxmysql 清除密码
1、以root登录：mysql -u root -p
2、mysql>use mysql;
3、mysql>update user set password='' where user='root';
备份 mysqldump -uroot -p db >1.sql
恢复 mysql -uroot -p db <1.sql
只备份一个表mysqldump -uroot -p db tb1 > 2.sql
备份时指定字符集 mysqldump -uroot -p --default-character-set=utf8db >1.sql
恢复也指定字符集 mysql -uroot -p --default-character-set=utf8db< 1.sql
页:
[1]