mysql @pdept_MySQL 基本管理与常用sql 归结

备份恢复的策略

1.确定要备份表的存储引擎是事务还是非事务的，不同的引擎备份方式在处理数据一致性方面

是不大一样的。

2.确定使用全备份还是增量备份。全备份的优点是备份保持最新备份，恢复的时候可以花费更少的

时间；缺点是如果数据量大，将花费很多的时间，并对系统造成长时间的压力。增量备份，只需备份每天

的增量日志，备份时间少，对负载的压力小；缺点是恢复的时候需要全备份加上次备份到故障前的所有日

志，恢复时间会长些。

3.可以考虑采取复制的方法做异地备份，但是复制不能替代备份，对数据库的误操作也是无能为力的。

4.要定期做备份，备份的周期要充分考虑系统可以承受的恢复时间。备份要在系统负载较小的时候进行。

5.确保MySQL打开log-bin选项，有了binlog,mysql才可以在必要的时候做完整恢复，或基于位置的恢复。

6.要经常做恢复测试，确保备份是有效的，并且是可以恢复的。

7.比如查看字符集：show character set ;

查看用户： select user from mysql.user ;

-------------------------------------------------------------------------------------------------------------------

改密码的命令

能登录mysql系统

1.update mysql.user set password = password('mima') where user='a'

2.flash privileges

不能登录系统在linux下

1.关闭mysql

2.mysqld_safe --skip-grant-tables --skip-networking

可以不要密码进行登录操作。

关闭mysql

# service mysqld stop

2.mysqld_safe --user=mysql --skip-grant-tables --skip-networking &

新开终端

# mysql -u root mysql

>use mysql

mysql> UPDATE user SET Password=PASSWORD('newpassword') where USER='root' and host='root' or host='localhost';

//把空的用户密码都修改成非空的密码就行了。

mysql> FLUSH PRIVILEGES;

mysql> quit # /etc/init.d/mysqld restart

# mysql -uroot -p

Enter password:

-------------------------------------------------------------------------------------------------------------------

查看MySQL的存储引擎

1.看你的mysql现在已提供什么存储引擎:

mysql> show engines;

2.看你的mysql当前默认的存储引擎:

mysql> show variables like '%storage_engine%';

3.mysql> show create table 表名;

--------------------------------------------------------------------------------------------------------

查看MySQL的版本

1.# mysql -V

mysql Ver 14.7 Distrib 4.1.10a, for redhat-linux-gnu (i686)

2：在mysql中：mysql> status;

以下是代码片段：

mysql> status;

--------------

mysql Ver 14.7 Distrib 4.1.10a, for redhat-linux-gnu (i686)

Connection id:          416

SSL:                    Not in use

Current pager:          stdout

Using outfile:          ''

Using delimiter:        ;

Server version:         3.23.56-log

Protocol version:       10

Connection:             Localhost via UNIX socket

Client characterset:    latin1

Server characterset:    latin1

UNIX socket:            /tmp/mysql_3311.sock

Uptime:                 62 days 21 hours 21 min 57 sec

Threads: 1 Questions: 584402560 Slow queries: 424 Opens: 59664208 Flush tables: 1 Open tables: 64 Queries per second avg: 107.551

3：在help里面查找

以下是代码片段：

[shengting@login ~]$ mysql --help | grep Distrib

mysql Ver 14.7 Distrib 4.1.10a, for redhat-linux-gnu (i686)

4：使用mysql的函数

以下是代码片段：

mysql> select version();

+-------------+

| version()   |

+-------------+

| 3.23.56-log |

+-------------+

1 row in set (0.00 sec)

--------------------------------------------------------------------------------------------------------------------

备份

备份分为逻辑本分和物理备份，逻辑本分的最大的优点是对于各种的存储引擎都可以用同样的方法

来备份；而物理备份则不同，不同的存储引擎有着不同的备份方法。因此对于不同存储引擎混合的数据库

，用逻辑备份会简单很多

在mysql中的逻辑备份是将数据库中的数据备份为一个文本文件，备份的文件可以编辑和查看，使用

mysqldump备份

1.备份指定的数据库，或者指定的数据库的某些表

shell>mysqldump [options] db_name [tables];

2.备份指定的一个或者多个数据库

shell>mysqldump [options] --database DB1 [DB2 DB3 ...]

3.备份所有的数据库

shell>mysqldump [options] --all-database ;

例子：

1.备份所有数据库

#mysqldump -uroot -p --all-database >all.sql

2.备份单个数据库

#mysqldump -uroot -p  db_name >db_name.sql

3.备份数据库下的单个表

#mysqldump -uroot -p db_name tablename > table.sql

4.备份数据库test下的emp,dept；

mysqldump -uroot -p test emp dept >emp_dept.sql

注意

为了保证数据备份的一致性，myisam 存储引擎在备份的时候需要加上-l 参数，表示将所有表加上读锁

在备份期间，所有表将只能读而不能进行数据更新。但是对于事物存储引擎(innodb和bdb)来说，可以采用

更好的选项--single-transaction ，此选项使得innodb存储引擎得到了一个快照(snapshot),使得备份的数据能

保持一致性。

————————————————————————————————————————————

还原部分

还原部分

(1)mysql命令行source方法 和 (2)系统命令行方法

1.还原全部数据库:

(1) mysql命令行：mysql>source f:\all.sql

(2) 系统命令行： mysql -uroot -p123456

2.还原单个数据库(需指定数据库)

(1) mysql>use mydb

mysql>source f:\mydb.sql

(2) mysql -uroot -p123456 mydb

3.还原单个数据库的多个表(需指定数据库)

(1) mysql>use mydb

mysql>source f:\multables.sql

(2) mysql -uroot -p123456 mydb

4.还原多个数据库，(一个备份文件里有多个数据库的备份，此时不需要指定数据库)

(1) mysql命令行：mysql>source f:\muldbs.sql

(2) 系统命令行： mysql -uroot -p123456

————————————————————————————————————————

增量备份还原

vi mysql的配置文件

vim  /etc/my.cnf

在[mysqld]

log-bin=binlog

log-bin-index=binlog.index

sync_binlog = 1                          ：(保证数据实时写到磁盘上)

重启mysql，开启binlog

查看binlog datadir 下的binlog.indix 告知那个是binlog

查看binlog  :mysqlbinlog binlog.000002

每个binlog中记录 1.at 数字(日志位置)2.时间  3.数据库改动的SQL

还原：mysqlbinlog  --start-position 185 --stop-position 272 binlog.000002 |mysql -u root -p

自动生成binlog在mysql下 >flush logs;

在linux下 mysql -e 'flush logs;'

mysql -e 'purge master logs to "binlog.000003"' 删除03 以前的binlog

full backup

还原

2.还原：在linux下 mysql -u root -p ab(库名) < /tmp/a.sql

在mysql的库下 source /tmp/a.sql;(Aborted

列表显示所有的binlog文件:MySQL> show binary logs;

显示当前的binlog文件:mysql>　SHOW BINLOG EVENTS \G ;

查看binlog文件 #mysqlbinlog binlog.000001

查看某个日志的文件mysql> show binlog events in 'mysql-bin.000021';

在rpm包安装的mysql  中binlog  目录 /var/lib/mysql

------------------------------------------------------------------------------------------------------

完全恢复

mysqldump 的恢复也很简单，将备份做为输入执行即可。

#mysqldump -uroot -p db_name

注意：将备份恢复后数据并不完整，还需要将备份后执行的日志进行重做。

mysqlbinlog binlog-file |mysql -u root -p

例子：

1.# mysqldump -uroot -p -l -F test >test.dmp

其中-l参数表示给所有表加读锁，-F 表示生成一个新的日志文件.

2.备份完毕，然后插入新数据

3.数据库出现故障，恢复数据库

#mysql -uroot -p test < test.dmp

查询数据发现更新的没有了

4.使用MySQLbinlog恢复自MySQLdump 备份以来的binlog

mysqlbinlog  binlog.00002 | MySQL -uroot -p test   验证出错。

按时间点恢复

mysqlbinlog  --start-position 185 --stop-position 272 binlog.000002 |mysql -u root -p

查询数据看看数据的完整性。

-------------------------------------------------------------------------------------------------

冷备

1.停掉mysql服务，在操作系统级别备份MySQL的数据文件和日志文件到备份目录。用cp

2恢复的时候

先停掉MySQL服务，在操作系统级别恢复MySQL的数据文件，然后重启MySQL服务，使用mysqlbinlog工具恢复

恢复自备份以来的所有binlog.

-----------------------------------------------------------------------------------------------------

mysql的权限管理

一.登录

用户名和密码 存放在mysql库的user表中

mysql -h ip -u root -p  默认不允许远程连接mysql数据库；

show grants for root@localhost;

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY ‘PASSWORD ’:创建用户，并赋权。

*(库).*(表) 'root(用户)'

flush privileges;快使设定权限生效。

revoke all privileges on *.* from 'mary'@'%'；

踢出 show processlist ;kill id号。

drop user 用户名；

grant select on ....赋予部分权限

grant usage on *.* to

mysql的权限，

列(.column_priv)----表(.tables_priv)------库(.db)---------多个库global(mysql.user)

--------------------------------------------------------------------------------------------------

常用sql

DDL

1.创建数据库

mysql>create database dbname;

----------------------------------------------------------------------------

>show databases;  查询mysql的数据库

>use dbname;   使用某个数据库

>show tables;  库里都有哪些表

>desc tablename; 查看表的字段

-----------------------------------------------------------------------

>drop table tbalename;删除表

>delete table tablename;删除表内所有的记录

————————————————————————————————————————

修改表

修改字段的数据类型

>alter table tablename modify columnname varchar(10)；写上更改后的数据类型即可

增加表字段

>alter table tablename add column columnname int;

删除表字段

>alter table tablename  drop  column columnname;

字段改名

> alter table  tablename change oldcolumnname  newcolumnname datatype;

更新记录

update tablename set field1=value1,field2=values2,....fieldn=valuen [where condition]

修改字段排列顺序

>alter table talename add columnname datatype after oldcolumnname;\

更改表名

>alter table oldtablename rename newtablename;

复制表

create table tablename as selct * from tablename2;

--------------------------------------------------------------------

主键外键约束在创建表时创建

主键

CREATE TABLE PLAYERS_XXL

(PLAYERNO     INTEGER NOT NULL PRIMARY KEY,

外键

create table  employee

(em_id  char(5)  not null,

em_name nvarchar2(20)  not null,

em_sex  char(1)  not null,

em_age  char(2)  not null,

dep_id   char(5)  not null,

primary key (em_id),

foreign key(dep_id) references department(dep_id));

单独创建主键外键

ALTER TABLE Customers ADD CONSTRAINT PK_Customers PRIMARY KEY (cust_id);

customers 的主键是cust_id

ALTER TABLE OrderItems

ADD CONSTRAINT FK_OrderItems_Orders FOREIGN KEY (order_num) REFERENCES Orders (order_num);

————————————————————————————————————

创建索引

CREATE INDEX PLAY_MAT (索引名)

ON     PLAYERS(PLAYERNO), MATCHES(PLAYERNO)

——————————————————————————————————

创建视图

CREATE   VIEW TOWNS AS

SELECT   DISTINCT TOWN

FROM     PLAYERS；

————————————————————————————————————

插入数据

insert into tablename( column1,column2) values (数值1，数值2)；

insert into tablename values ( )，()；全字段赋值

insert into test1 select * from test1;

----------------------------------------------------------------------------

创建存储过程

create or replace procedure pro_aa(salekind in int,salesum in int)

as

var_sum2 int:= 0;

begin

select product_sum into var_sum2 from t1 where product_id=salekind;

if var_sum2 >= salesum

then

update t1 set product_sum=product_sum-salesum  where product_id=salekind;

update t2 set sale_sum=sale_sum + salesum where product_id=salekind;

dbms_output.put_line('jiao yi shu liang que ren');

else

dbms_output.put_line('ku cun liang bu zu');

end if;

end;

/

-----------------------------------------------------------------------

创建函数

-----------------------------------------------------------------------------------------

1.修改mysql中root的密码：

shell>mysql -u root -p

mysql>SET PASSWORD FOR root=PASSWORD("root");

2.远程登录mysql server：(当然server必须首先给某个远程用户授权了)

shell>mysql -h host/ip -u user -p

3.打开数据库：use dbname；

显示所有数据库：show databases;

显示数据库mysql中所有的表：先use mysql；然后show tables;

显示表的列信息：describe user;(显示表mysql数据库中user表的信息)；

4.创建一个可以从任何地方连接服务器的一个完全的超级用户，但是必须使用一个口令something做这个

GRANT ALL PRIVILEGES ON *.* TO monty@localhost IDENTIFIED BY 'something' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON *.* TO monty@"%" IDENTIFIED BY 'something' WITH GRANT OPTION;

flash privileges;

5.删除授权：

REVOKE ALL PRIVILEGES ON *.* FROM root@"%";

USE mysql;

DELETE FROM user WHERE User="root" and Host="%";

FLUSH PRIVILEGES;

6. 创建一个用户custom在特定客户端weiqiong.com登录，可访问特定数据库bankaccount

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON bankaccount.*

TO custom@weiqiong.com IDENTIFIED BY 'stupid';

7.重命名表:

ALTER TABLE t1 RENAME t2;

为了改变列a，从INTEGER改为TINYINT NOT NULL(名字一样)，

并且改变列b，从CHAR(10)改为CHAR(20)，同时重命名它，从b改为c:

ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);

增加一个新TIMESTAMP列，名为d：

ALTER TABLE t2 ADD d TIMESTAMP;

在列d上增加一个索引，并且使列a为主键：

ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);

删除列c：

ALTER TABLE t2 DROP COLUMN c;

增加一个新的AUTO_INCREMENT整数列，命名为c：

ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,ADD INDEX (c);

注意，我们索引了c，因为AUTO_INCREMENT柱必须被索引，并且另外我们声明c为NOT NULL，

因为索引了的列不能是NULL。

8.删除记录:

DELETE FROM t1 WHERE C>10;

6.改变某几行:

UPDATE t1 SET user=weiqiong,password=weiqiong;

7.使用name列的头10个字符创建一个索引:

CREATE INDEX part_of_name ON customer (name(10));