mysql工作_mysql日常工作手记(持续更新)

最新推荐文章于 2023-02-01 23:42:22 发布

天天IT

最新推荐文章于 2023-02-01 23:42:22 发布

阅读量143

点赞数

文章标签： mysql工作

本文链接：https://blog.csdn.net/weixin_42617150/article/details/113130607

版权

1、给navy加show权限：update mysql.user set Show_db_priv='Y' where user='navy';

flush privileges;

2、查询服务器系统变量值：mysqladmin -h 127.0.0.1 -uroot -p123456 --sock=logs/mysql.sock variables

3、显示服务器所有运行的进程(5s刷新一次)：mysqladmin -h 127.0.0.1 -uroot -p123456 --sock=logs/mysql.sock -i 5 processlist

4、重载权限信息：mysqladmin -uroot -p reload

5、mysqladmin执行kill进程：./bin/mysqladmin -h 127.0.0.1 -uroot -pking+5688 --sock=logs/mysql.sock kill 610(610为processlist看到的sql ID号)

6、导出数据库navy2：./bin/mysqldump -h 127.0.0.1 -P 3307 --sock=logs/mysql.sock navy2 > /data/navy2.sql

7、导出数据库navy2中的test表：./bin/mysqldump -h 127.0.0.1 -P 3307 --sock=logs/mysql.sock navy2 test >/data/test.sql

8、导入数据库navy2.sql到navy1数据库：./bin/mysql -h 127.0.0.1 -uroot -pking+5688 --sock=logs/mysql.sock navy1

use navy1;source /data/navy2.sql(方法二)

9、MYISAMVSINNODB：MyisAM :支持全文索引，压缩索引，键值分离，一般用在读上面

InnoDB: 支持事务，键值在一起被缓存在innodb缓存池里，一般用在写或读写

10、mysql清理binlog：mysql>show binary logs;

mysql>purge binary logs to 'mysql-bin.000055'; 从最原始清理到这个点

11、重新生成一个二进制日志文件flush logs;

12、看你的mysql现在已提供什么存储引擎:mysql> show engines;

13、查看你的mysql当前默认的存储引擎:mysql> show variables like '%storage_engine%';

14、字符集相关：查看数据库的字符集：

show variables like 'character%';

修改数据库的字符集：

alter database mydb character set utf8;

创建数据库时指定字符集：

create database mydb character set utf8;

15、锁表(只读，不可写)flush tables with read lock;

16、解锁unlock tables;

17、主从同步相关：查看master的binlog信息

show master status\G

查看同步信息

show slave status\G

选择同步点，主库ip是192.168.0.117，端口3307，同步账号slave，密码123456，主库binlog文件mysql-bin.000067，偏移点107

change master to master_host='192.168.0.117', master_Port=3307, master_user='slave', master_password='123456', master_log_file='mysql-bin.000067', master_log_pos=107;

创建账号slave，密码123456，权限“replication slave”，开放ip是192.168.0.119的账号

grant replication slave on *.* to slave@192.168.0.119 identified by "123456";

删除账号

drop user slave@192.168.0.119;

18、从库跳过一条binlog(需要先slavestop;)SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

19、导出binlog文件到定义文件6.sqlmysqlbinlog mysql-bin.000006 > /data/sql_bak/6.sql

20、修改mysql的root密码(密码忘记)1.kill掉系统里的mysql进程 (ps | grep mysqld)

2.用以下命令启动mysql，以不检查权限的方式启动

/usr/local/mysql/bin/mysqld_safe --skip-grant-tables &

3.然后用空密码方式使用root用户登录mysql

mysql -u root

4.修改root用户的密码：

mysql> update mysql.user set password=PASSWORD('310126')where User='root';

mysql> quit

5.重新启动mysql，就可以用新密码登录了

21、新开库需要开的权限：grant select,insert,update,delete on newdatabase.* to newdatabase_pro@'10.10.0.%' Identified by "123456“;(应用程序连的用户)

grant all on newdatabase.* to newdatabase@10.10.0.100 Identified by "123456";(管理员用户)

grant replication slave on *.* to slave@10.10.0.10 identified by "123456";(主从复制用户)

grant replication client on *.* to repl_cli@'192.168.0.%' identified by "123456";(监控用户)

flush privileges;(刷新权限)

22、查看用户slave的权限：show Grants for slave@172.28.26.188;

23、修改用户密码的三种方法：1、set password for 'root'@'127.0.0.1' = password('123456')

2、mysqladmin -u root password "newpass"

3、UPDATE user SET Password = PASSWORD('newpass') WHERE user = 'root';

24、MySql修改表结构语句：ALTER TABLE：添加，修改，删除表的列，约束等表的定义。

查看列：desc 表名;

修改表名：alter table t_book rename to bbb;

添加列：alter table 表名 add column 列名 varchar(30);

删除列：alter table 表名 drop column 列名;

修改列名MySQL： alter table bbb change nnnnn hh int;

修改列名SQLServer：exec sp_rename't_student.name','nn','column';

修改列名Oracle：lter table bbb rename column nnnnn to hh int;

修改列属性：alter table t_book modify name varchar(22);加template_id字段，位于category_id之后

ALTER TABLE `common_atlases` add `template_id` int(11) NOT NULL DEFAULT '0' COMMENT '模板id' AFTER `category_id`;

修改status字段，使status字段位于classid字段之后

ALTER TABLE `common_atlases` CHANGE `status` `status` int(11) NOT NULL DEFAULT '1' COMMENT '是否显示' AFTER `classid` ;

25、函数创建、查看、删除

查看函数是否开启：

mysql> show variables like '%func%';

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

| Variable_name | Value |

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

| log_bin_trust_function_creators | ON |

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

1 row in set (0.00 sec)

启用函数：

mysql> set global log_bin_trust_function_creators=1;

创建函数：

mysql> use web_platform

Database changed

mysql> DELIMITER $$

mysql> CREATE DEFINER=`web_platform_pro`@`10.10.0.%` FUNCTION `currval`(`s_seq_name` VARCHAR(20)) RETURNS int(11)

-> BEGIN

-> DECLARE n_value INTEGER;

-> SET n_value = 0;

-> SELECT current_value INTO n_value

-> FROM t_sequence

-> WHERE name = s_seq_name;

-> RETURN n_value;

-> END

-> $$

Query OK, 0 rows affected, 1 warning (0.00 sec)

PS:$$是设置 $$为命令终止符号，代替分号，因为分号在begin...end中会用到；

查看函数：

mysql> show function status\G

*************************** 1. row ***************************

Db: web_platform

Name: currval

Type: FUNCTION

Definer: web_platform_pro@10.10.0.%

Modified: 2013-11-01 20:35:42

Created: 2013-11-01 20:35:42

Security_type: DEFINER

Comment:

character_set_client: latin1

collation_connection: latin1_swedish_ci

Database Collation: utf8_general_ci

删除函数：

mysql> drop function currval;

Query OK, 0 rows affected (0.00 sec)

26、查看数据库QPS、TPSmysqladmin -uroot -p123456 -h127.0.0.1 -P13306 extended-status -i1 | awk 'BEGIN {switch=0;print "QPS Commit Rollback TPS Threads_con Threads_run \n------------------------------------------------------- "}

$2 ~ /Queries$/ {q=$4-lq;lq=$4;}

$2 ~ /Com_commit$/ {c=$4-lc;lc=$4;}

$2 ~ /Com_rollback$/ {r=$4-lr;lr=$4;}

$2 ~ /Threads_connected$/ {tc=$4;}

$2 ~ /Threads_running$/ {tr=$4;if(switch==0) switch=1; else printf "%-6d %-8d %-7d %-8d %-10d %d \n", q,c,r,c+r,tc,tr;}'

27、用binlog数据库

#!/bin/sh

cd /data/mysql/navy #数据库的安装目录

for ((i=1000;i<=2004;i++)); do

echo -e "`date`\nmysql-bin.00${i} start." >> /data/logs/navy_bak.log

./bin/mysqlbinlog -d navy /data/binlog/mysql-bin.00${i} | ./bin/mysql -f -uroot -p123456 --sock=logs/mysql.sock navy

done

28、库里面已经有user1@10.10.1.100，现在要加一个用户user2@10.10.1.101，要求和user1的密码一样，但是user1的密码不知道，可以这样弄：

show grants for navy1@'10.10.1.100'; 查看navy1加密过的密码(*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9)

grant all on *.* to navy2@'10.10.1.101' identified by PASSWORD "*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9"; 创建navy2(PASSWORD是上面语句查到的navy1的加密密码)

29、构造创建索引的sql(db_name替换成你的库名)select concat('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' add index ',INDEX_NAME,' on (',group_concat(COLUMN_NAME order by null),');') from information_schema.STATISTICS where table_schema='db_name' and INDEX_NAME<>'PRIMARY' group by INDEX_NAME order by SEQ_IN_INDEX ;

30、构造创建索引的sql(db_name替换成你的库名)，把sql输出到文件里面select concat('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' add index ',INDEX_NAME,' on (',group_concat(COLUMN_NAME order by null),');') as create_index into outfile '/tmp/create.sql' from information_schema.STATISTICS where table_schema='db_name' and INDEX_NAME<>'PRIMARY' group by INDEX_NAME order by SEQ_IN_INDEX ;

31、在MySQL中拷贝表的几种方式

A、下面这个语句会拷贝表结构到新表newadmin中。 (不会拷贝表中的数据)CREATE TABLE newadmin LIKE admin

B、下面这个语句会拷贝数据到新表中。注意：这个语句其实只是把select语句的结果建一个表。所以newadmin这个表不会有主键，索引。CREATE TABLE newadmin AS

(

SELECT *

FROM admin

)

C、如果你要真正的复制一个表。可以用下面的语句。CREATE TABLE newadmin LIKE admin;

INSERT INTO newadmin SELECT * FROM admin;

D、我们可以操作不同的数据库。CREATE TABLE newadmin LIKE shop.admin;

CREATE TABLE newshop.newadmin LIKE shop.admin;

E、我们也可以拷贝一个表中其中的一些字段。CREATE TABLE newadmin AS

(

SELECT username, password FROM admin

)

F、我们也可以讲新建的表的字段改名。CREATE TABLE newadmin AS

(

SELECT id, username AS uname, password AS pass FROM admin

)

G、我们也可以拷贝一部分数据。CREATE TABLE newadmin AS

(

SELECT * FROM admin WHERE LEFT(username,1) = 's'

)

H、我们也可以在创建表的同时定义表中的字段信息。CREATE TABLE newadmin

(

id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY

)

(

SELECT * FROM admin

)

32、储存过程1、创建:

reate procedure procedure_name() select * from table_name;

2、调用:

call procedure_name;

3、查看:

查看过程的内容有三种方法:

a、show create procedure procedure_name \G 显示一个储存过程的详细信息

b、show procedure status like 'procedure_name'

c、select * from mysql.proc where name='procedure_name'

4、删除：

drop procedure procedure_name;

33、MySQL计划任务(事件调度器)(Event Scheduler)SHOW VARIABLES LIKE 'event_scheduler'; 查看event是否打开

SET GLOBAL event_scheduler = 1;或 SET GLOBAL event_scheduler = ON; 开启event

show events\G 查看event

show create event day_delete_cnpnews_info \G 查看具体一条event的详细信息

drop event day_delete_cnpnews_info 删除event

34、分批删数据#!/bin/sh

while true

mysql -uroot -p123456 -h 127.0.0.1 -P10023 -e "use dbname;delete FROM table WHERE ID <597021 limit 100000;"

sleep 10s

done

天天IT

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql工作_mysql日常工作手记(持续更新)

1、给navy加show权限：updatemysql.usersetShow_db_priv='Y'whereuser='navy';flushprivileges;2、查询服务器系统变量值：mysqladmin-h127.0.0.1-uroot-p123456--sock=logs/mysql.sockvariables3、显示服务器所有运行的进程(5s刷新一次)：my...
复制链接

扫一扫