|
|
一、mysql数据库用户密码修改方法
(1)、在知道mysql数据库root用户密码条件下修改root用户密码wxsemico
方法一：
[iyunv@centos6 ~]# mysql -u root -p
Enter password: 输入root密码
mysql> show databases;
mysql> use mysql;
mysql> update user set password=password('wxsemico') where user='root';
mysql> flush privileges;
mysql> exit
方法二：
[iyunv@centos6 ~]# mysqladmin -u root -p2013@wxsemico password wxsemico
/红色原密码，蓝色新密码
[iyunv@centos6 ~]# mysql -u root -pwxsemico
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 386
Server version: 5.1.42-log Source distribution
Copyright (c) 2000, 2012, 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>
/使用新密码wxsemico登录成功
(3)、在忘记mysql数据库root用户密码条件下修改root用户密码为2013@wxsemico
[iyunv@centos6 ~]# mysqladmin -u root -p shutdown
Enter password:
140523 18:50:33 mysqld_safe mysqld from pid file /usr/local/mysql/var/centos6.pid ended
[1]+ Done ./mysqld_safe --user=root --skip-grant-tables (wd: /usr/local/mysql/bin)
(wd now: ~)
[iyunv@centos6 ~]# ps -ef | grep mysql
root 2751 1971 0 18:50 pts/0 00:00:00 grep mysql
/先关闭掉mysql数据库服务
/由于mysql为源码安装方式先切换至mysql安装目录下的bin目录
[iyunv@centos6 ~]# cd /usr/local/mysql/bin/
[iyunv@centos6 bin]# ll
total 16600
-rwxr-xr-x. 1 mysql mysql 10664 May 22 2013 innochecksum
-rwxr-xr-x. 1 mysql mysql 1424 May 22 2013 msql2mysql
-rwxr-xr-x. 1 mysql mysql 1794627 May 22 2013 myisamchk
-rwxr-xr-x. 1 mysql mysql 1667899 May 22 2013 myisam_ftdump
-rwxr-xr-x. 1 mysql mysql 1667299 May 22 2013 myisamlog
-rwxr-xr-x. 1 mysql mysql 1711644 May 22 2013 myisampack
-rwxr-xr-x. 1 mysql mysql 1398357 May 22 2013 my_print_defaults
-rwxr-xr-x. 1 mysql mysql 269500 May 22 2013 mysql
-rwxr-xr-x. 1 mysql mysql 110848 May 22 2013 mysqlaccess
-rwxr-xr-x. 1 mysql mysql 31997 May 22 2013 mysqladmin
-rwxr-xr-x. 1 mysql mysql 157601 May 22 2013 mysqlbinlog
-rwxr-xr-x. 1 mysql mysql 10675 May 22 2013 mysqlbug
-rwxr-xr-x. 1 mysql mysql 26276 May 22 2013 mysqlcheck
-rwxr-xr-x. 1 mysql mysql 415234 May 22 2013 mysql_client_test
-rwxr-xr-x. 1 mysql mysql 6137 May 22 2013 mysql_config
-rwxr-xr-x. 1 mysql mysql 4169 May 22 2013 mysql_convert_table_format
-rwxr-xr-x. 1 mysql mysql 23253 May 22 2013 mysqld_multi
-rwxr-xr-x. 1 mysql mysql 16690 May 22 2013 mysqld_safe
-rwxr-xr-x. 1 mysql mysql 91933 May 22 2013 mysqldump
-rwxr-xr-x. 1 mysql mysql 6602 May 22 2013 mysqldumpslow
-rwxr-xr-x. 1 mysql mysql 3245 May 22 2013 mysql_find_rows
-rwxr-xr-x. 1 mysql mysql 483 May 22 2013 mysql_fix_extensions
-rwxr-xr-x. 1 mysql mysql 5894 May 22 2013 mysql_fix_privilege_tables
-rwxr-xr-x. 1 mysql mysql 31485 May 22 2013 mysqlhotcopy
-rwxr-xr-x. 1 mysql mysql 26643 May 22 2013 mysqlimport
-rwxr-xr-x. 1 mysql mysql 14623 May 22 2013 mysql_install_db
-rwxr-xr-x. 1 mysql mysql 7694 May 22 2013 mysql_secure_installation
-rwxr-xr-x. 1 mysql mysql 16689 May 22 2013 mysql_setpermission
-rwxr-xr-x. 1 mysql mysql 24611 May 22 2013 mysqlshow
-rwxr-xr-x. 1 mysql mysql 46541 May 22 2013 mysqlslap
-rwxr-xr-x. 1 mysql mysql 192366 May 22 2013 mysqltest
-rwxr-xr-x. 1 mysql mysql 1368006 May 22 2013 mysql_tzinfo_to_sql
-rwxr-xr-x. 1 mysql mysql 65042 May 22 2013 mysql_upgrade
-rwxr-xr-x. 1 mysql mysql 159255 May 22 2013 mysql_waitpid
-rwxr-xr-x. 1 mysql mysql 3818 May 22 2013 mysql_zap
-rwxr-xr-x. 1 mysql mysql 1388574 May 22 2013 perror
-rwxr-xr-x. 1 mysql mysql 1380024 May 22 2013 replace
-rwxr-xr-x. 1 mysql mysql 1379698 May 22 2013 resolveip
-rwxr-xr-x. 1 mysql mysql 1381612 May 22 2013 resolve_stack_dump
[iyunv@centos6 bin]# ./mysqld_safe --user=root --skip-grant-tables & /忽略授权表
[1] 2778
[iyunv@centos6 bin]# 140523 18:55:56 mysqld_safe Logging to '/usr/local/mysql/var/centos6.err'.
140523 18:55:57 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var
[iyunv@centos6 bin]# cd /切换至root家目录
[iyunv@centos6 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.42-log Source distribution
Copyright (c) 2000, 2012, 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>
/无需密码即可登录mysql数据库，通过前面的方法重新设置root用户新密码即可！
[iyunv@centos6 bin]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.42-log Source distribution
Copyright (c) 2000, 2012, 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> select version();
+------------+
| version() |
+------------+
| 5.1.42-log |
+------------+
1 row in set (0.00 sec)
/查询mysql数据库版本
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
/查询当前登录用户
mysql> select user from mysql.user;
+-------+
| user |
+-------+
| cacti |
| root |
| root |
| |
| root |
| |
| cacti |
| root |
| user1 |
+-------+
9 rows in set (0.00 sec)
mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*D4CD521B67294775430C15B348B47051EBA4B810' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `cacti`.* TO 'root'@'localhost' |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
/查看授权情况
mysql> grant select,insert,update,delete on *.* to zhang3@localhost identified by "zhang3";
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
/增加一个用户zhang3密码也为zhang3，让它可以从本机登录并对所有数据库有查询、插入、修改、删除权限
mysql> exit
[iyunv@centos6 ~]# mysql -u zhang3 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 448
Server version: 5.1.42-log Source distribution
Copyright (c) 2000, 2012, 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> select user();
+------------------+
| user() |
+------------------+
| zhang3@localhost |
+------------------+
1 row in set (0.00 sec)
/新建用户zhang3登录数据库成功
二、如何封装源码安装的mysql为系统服务
[iyunv@centos6 ~]# cd /usr/local/mysql/share/mysql/
[iyunv@centos6 mysql]# ll
total 1268
-rw-r--r--. 1 mysql mysql 1153 May 22 2013 binary-configure
drwxr-xr-x. 2 mysql mysql 4096 May 22 2013 charsets
-rw-r--r--. 1 mysql mysql 3805 May 22 2013 config.huge.ini
-rw-r--r--. 1 mysql mysql 1661 May 22 2013 config.medium.ini
-rw-r--r--. 1 mysql mysql 905 May 22 2013 config.small.ini
drwxr-xr-x. 2 mysql mysql 4096 May 22 2013 czech
drwxr-xr-x. 2 mysql mysql 4096 May 22 2013 danish
drwxr-xr-x. 2 mysql mysql 4096 May 22 2013 dutch
drwxr-xr-x. 2 mysql mysql 4096 May 22 2013 english
-rw-r--r--. 1 mysql mysql 416006 May 22 2013 errmsg.txt
drwxr-xr-x. 2 mysql mysql 4096 May 22 2013 estonian
-rw-r--r--. 1 mysql mysql 591883 May 22 2013 fill_help_tables.sql
drwxr-xr-x. 2 mysql mysql 4096 May 22 2013 french
drwxr-xr-x. 2 mysql mysql 4096 May 22 2013 german
drwxr-xr-x. 2 mysql mysql 4096 May 22 2013 greek
drwxr-xr-x. 2 mysql mysql 4096 May 22 2013 hungarian
drwxr-xr-x. 2 mysql mysql 4096 May 22 2013 italian
drwxr-xr-x. 2 mysql mysql 4096 May 22 2013 japanese
drwxr-xr-x. 2 mysql mysql 4096 May 22 2013 korean
-rw-r--r--. 1 mysql mysql 5006 May 22 2013 mi_test_all
-rw-r--r--. 1 mysql mysql 1517 May 22 2013 mi_test_all.res
-rw-r--r--. 1 mysql mysql 4778 May 22 2013 my-huge.cnf
-rw-r--r--. 1 mysql mysql 20167 May 22 2013 my-innodb-heavy-4G.cnf
-rw-r--r--. 1 mysql mysql 4752 May 22 2013 my-large.cnf
-rw-r--r--. 1 mysql mysql 4763 May 22 2013 my-medium.cnf
-rw-r--r--. 1 mysql mysql 2401 May 22 2013 my-small.cnf
-rwxr-xr-x. 1 mysql mysql 1061 May 22 2013 mysqld_multi.server
-rw-r--r--. 1 mysql mysql 42249 May 22 2013 mysql_fix_privilege_tables.sql
-rw-r--r--. 1 mysql mysql 837 May 22 2013 mysql-log-rotate
-rwxr-xr-x. 1 mysql mysql 12302 May 22 2013 mysql.server
-rw-r--r--. 1 mysql mysql 1878 May 22 2013 mysql_system_tables_data.sql
-rw-r--r--. 1 mysql mysql 16527 May 22 2013 mysql_system_tables.sql
-rw-r--r--. 1 mysql mysql 9669 May 22 2013 mysql_test_data_timezone.sql
-rw-r--r--. 1 mysql mysql 589 May 22 2013 ndb-config-2-node.ini
drwxr-xr-x. 2 mysql mysql 4096 May 22 2013 norwegian
drwxr-xr-x. 2 mysql mysql 4096 May 22 2013 norwegian-ny
drwxr-xr-x. 2 mysql mysql 4096 May 22 2013 polish
drwxr-xr-x. 2 mysql mysql 4096 May 22 2013 portuguese
drwxr-xr-x. 2 mysql mysql 4096 May 22 2013 romanian
drwxr-xr-x. 2 mysql mysql 4096 May 22 2013 russian
drwxr-xr-x. 2 mysql mysql 4096 May 22 2013 serbian
drwxr-xr-x. 2 mysql mysql 4096 May 22 2013 slovak
drwxr-xr-x. 2 mysql mysql 4096 May 22 2013 spanish
drwxr-xr-x. 2 mysql mysql 4096 May 22 2013 swedish
drwxr-xr-x. 2 mysql mysql 4096 May 22 2013 ukrainian
[iyunv@centos6 mysql]# cp mysql.server /etc/rc.d/init.d/mysql
[iyunv@centos6 init.d]# chkconfig --list | grep mysql
[iyunv@centos6 init.d]# chkconfig --add mysql
[iyunv@centos6 init.d]# chkconfig --list | grep mysql
mysql 0:off 1:off 2:on 3:on 4:on 5:on 6:off
[iyunv@centos6 init.d]# service mysql start
Starting MySQL....[ OK ]
[iyunv@centos6 init.d]# ps -ef | grep mysql
root 2184 1 0 23:34 pts/0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/var --pid-file=/usr/local/mysql/var/centos6.pid
mysql 2287 2184 0 23:34 pts/0 00:00:00 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var --user=mysql --log-
error=/usr/local/mysql/var/centos6.err --pid-file=/usr/local/mysql/var/centos6.pid --socket=/tmp/mysql.sock --port=3306
root 2329 1849 0 23:35 pts/0 00:00:00 grep mysql
[iyunv@centos6 init.d]# service mysql stop
Shutting down MySQL.[ OK ]
[iyunv@centos6 init.d]# ps -ef | grep mysql
root 2356 1849 0 23:35 pts/0 00:00:00 grep mysql
三、数据库的备份和恢复
[iyunv@centos6 ~]# mysql -uroot -pwxsemico
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1218
Server version: 5.1.42-log Source distribution
Copyright (c) 2000, 2012, 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cacti |
| mysql |
| test |
| vpn |
+--------------------+
5 rows in set (0.00 sec)
mysql> use vpn;
Database changed
mysql> show tables;
+---------------+
| Tables_in_vpn |
+---------------+
| vpnuser |
+---------------+
1 row in set (0.00 sec)
mysql> desc vpnuser;
+----------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-----------+------+-----+---------+-------+
| name | char(20) | NO | PRI | NULL | |
| password | char(128) | YES | | NULL | |
| active | int(10) | NO | | 1 | |
+----------+-----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> select * from vpnuser;
+--------+-------------------------------------------+--------+
| name | password | active |
+--------+-------------------------------------------+--------+
| user1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1 |
| user2 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1 |
| user3 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1 |
| zhang3 | zhang123 | 1 |
+--------+-------------------------------------------+--------+
4 rows in set (0.00 sec)
(1)、数据库vpn备份
root@centos6 ~]# mkdir /backup
[iyunv@centos6 ~]# mysqldump -uroot -pwxsemico vpn > /backup/vpn_bk.sql
[iyunv@centos6 ~]# mysqldump -uroot -pwxsemico vpn | gzip > /backup/vpn_bk.sql
[iyunv@centos6 ~]# cd /backup
[iyunv@centos6 backup]# ll
total 8
-rw-r--r-- 1 root root 776 May 24 03:05 vpn_bk.sql
-rw-r--r-- 1 root root 776 May 24 03:08 vpn_bk.sql.gz
/如若需要备份的数据库容量较大，可采用gzip压缩方式来减少容量
(2)、删除数据库vpn中vpnuser表后恢复数据
mysql> drop table vpnuser;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from vpn.vpnuser;
ERROR 1146 (42S02): Table 'vpn.vpnuser' doesn't exist
mysql> exit
Bye
[iyunv@centos6 ~]# mysql -u root -pwxsemico vpn < /backup/vpn_bk.sql
[iyunv@centos6 ~]# mysql -u root -pwxsemico
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1497
Server version: 5.1.42-log Source distribution
Copyright (c) 2000, 2012, 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cacti |
| mysql |
| test |
| vpn |
+--------------------+
5 rows in set (0.00 sec)
mysql> select * from vpn.vpnuser;
+--------+-------------------------------------------+--------+
| name | password | active |
+--------+-------------------------------------------+--------+
| user1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1 |
| user2 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1 |
| user3 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1 |
| zhang3 | zhang123 | 1 |
+--------+-------------------------------------------+--------+
4 rows in set (0.00 sec)
/数据库vpn中vpnuser表及其数据恢复成功
(3)、直接删除vpn数据库后其数据恢复
[iyunv@centos6 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1558
Server version: 5.1.42-log Source distribution
Copyright (c) 2000, 2012, 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cacti |
| mysql |
| test |
| vpn |
+--------------------+
5 rows in set (0.00 sec)
mysql> drop database vpn;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cacti |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> exit
Bye
[iyunv@centos6 ~]# mysql -uroot -pwxsemico vpn < /backup/vpn_bk.sql
ERROR 1049 (42000): Unknown database 'vpn'
[iyunv@centos6 ~]# mysql -uroot -pwxsemico
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1572
Server version: 5.1.42-log Source distribution
Copyright (c) 2000, 2012, 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> create database vpn;
Query OK, 1 row affected (0.00 sec)
/删除vpn数据库后恢复数据库之前需先创建vpn数据库
mysql> exit
Bye
[iyunv@centos6 ~]# mysql -uroot -pwxsemico vpn < /backup/vpn_bk.sql
[iyunv@centos6 ~]# mysql -u root -pwxsemico
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1580
Server version: 5.1.42-log Source distribution
Copyright (c) 2000, 2012, 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cacti |
| mysql |
| test |
| vpn |
+--------------------+
5 rows in set (0.00 sec)
mysql> select * from vpn.vpnuser;
+--------+-------------------------------------------+--------+
| name | password | active |
+--------+-------------------------------------------+--------+
| user1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1 |
| user2 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1 |
| user3 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1 |
| zhang3 | zhang123 | 1 |
+--------+-------------------------------------------+--------+
4 rows in set (0.00 sec)
/vpn数据库及其vpnuser表中的数据恢复成功
四、其他相关操作
show status
/显示数据库的运行状态
show databases;
/显示数据库列表
use mysql;
/打开mysql数据库
show tables;
/显示mysql数据库中的数据表
describle 表名；
/显示数据表的结构
create database 库名；
/创建特定名称数据库
drop database 库名；
/删除特定名称数据库
create table 表名(字段设定列表）
/创建数据库中的特定表
drop table 表名；
/删除特定名称的表
delete from 表名；
/清空表记录
select * from 表名；
/显示表中的记录
insert into 表名 values(， ，)
/插入值到表中
alter table 表名 add column <字段名><字段选项>
/修改字段
|
|
QQ群⑧：
窥视卡
雷达卡
发表于 2014-6-27 16:47:43
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡