--如果你想允许用户myuser从ip为10.10.168.104的主机连接到mysql服务器，并使用zxtest135作为密码
---给TEST的访问权限


grant all PRIVILEGES on test.* to 'zxlhtest'@'%' identified by 'zxlhtest';
--给root的访问权限


GRANT ALL PRIVILEGES ON *.* TO  'root'@'%' IDENTIFIED BY  'zxtest135' WITH GRANT OPTION;








GRANT ALL PRIVILEGES ON *.* TO  'root'@'10.10.168.104' IDENTIFIED BY  'zxtest246' WITH GRANT OPTION;


-----新建用户及授权
--新建用户
CREATE USER 'zxlhtest2'@'172.16.2.152' IDENTIFIED BY 'zxlhtest';--访问172.16.2.152
GRANT GRANT OPTION ON *.* TO 'zxlhtest2'@'172.16.2.152';


GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'zxlhtest'@'172.16.2.152';


GRANT SELECT, INSERT, UPDATE, REFERENCES, DELETE, CREATE, DROP, ALTER, INDEX, TRIGGER, CREATE VIEW, SHOW VIEW, EXECUTE, ALTER ROUTINE, CREATE ROUTINE, CREATE TEMPORARY TABLES, LOCK TABLES, EVENT ON `test`.* TO 'zxlhtest2'@'172.16.2.152';


GRANT GRANT OPTION ON `test`.* TO 'zxlhtest2'@'172.16.2.152';






mysql -u zxlhtest2 -p


--建数据库
create database zxlh_import;


--修改用户名，安装完成mysql需要修改
update user set password=password('zxtest246') where user='root';
--删除数据库
drop database zxlh；


--查看硬盘
df -hl


--添加自增字段 及 初始值
alter table corp_black MODIFY corp_blackID bigINT(20) UNSIGNED AUTO_INCREMENT;
alter table corp_black auto_increment=1000000000000;


MySQL Migration Toolkit：数据库迁移 
MySQL Administrator：MySQL管理器 
MySQL Query Browser：用于数据查询的图形化客户端 
MySQL Workbench：DB Design工具




---mysql调用过程
--有输出的参数
call pro3('222','aaaaaaaaffffffffffffffffffffffffffffffffffafffffffffffffffff',@c);
select @c;




--生产环境 账户密码
mysqladmin -u root password 123456
root  123456


---库zxlh utf-8字符集
create database zxlh default character set utf8;


--远程访问的用户 只访问zxlh
grant all PRIVILEGES on zxlh.* to 'zxlh2'@'%' identified by 'zxlh2';


--远程dba权限 root 访问所有
grant all PRIVILEGES on *.* to 'root'@'%' identified by '123456' WITH GRANT OPTION;


--刷新
flush privileges;


--传输数据
scp /etc/zheng.war WZZXlhl@10.50.28.21:/usr/local/tomcat/webapps/Zheng.war
scp /etc/CorpBaseControl.class WZZXlhl@10.50.28.21:/usr/local/tomcat/webapps/Zheng/WEB-INF/classes/com/upg/zx/control/CorpBaseControl.class


scp /etc/test.jsp WZZXlhl@10.50.28.21:/usr/local/tomcat/webapps/Zheng/WEB-INF/jsp/test.jsp


scp /etc/wxIndex.jsp WZZXlhl@10.50.28.21:/usr/local/tomcat/webapps/Zheng/WEB-INF/jsp/wxIndex.jsp






/usr/local/tomcat/webapps/Zheng/resources/static/dist/pages/index/js
--查找文件
 find / -name my.cnf
 
  find / -name mysql.sock
 
 --删除路径
 rm -r xxx


 --停止服务
cd /usr/local/tomcat/bin
./shutdown.sh
--开启服务
cd /usr/local/tomcat/bin
 ./startup.sh
 
 
 
 
tar  -zxvf   压缩文件名.tar.gz /aa/bb
#解压到指定目录
unzip -o -d /usr/local/tomcat/webapps/ROOT ./web.zip


zip压缩
zip  -r ./Zheng.zip ./Zheng
-r 递归
zip 递归 要压缩的相对路径  相对路径的文件


mysql
--查看计划是否启动
 show variables like 'event_scheduler';
 
 --设置计划启动
 SET GLOBAL event_scheduler=ON;
 
 --查看端口号
 netstat -npl|grep 8080
 
 tcp      0    0 :::8080     :::*       LISTEN      24659/java
 Kill -9 24659
 
 --设置
 SET GLOBAL log_bin_trust_function_creators = 1;
 
 出错信息：
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
 
原因：
这是我们开启了bin-log, 我们就必须指定我们的函数是否是
1 DETERMINISTIC 不确定的
2 NO SQL 没有SQl语句，当然也不会修改数据
3 READS SQL DATA 只是读取数据，当然也不会修改数据
4 MODIFIES SQL DATA 要修改数据
5 CONTAINS SQL 包含了SQL语句


其中在function里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。
解决方法：
SQL code
mysql> show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
mysql> set global log_bin_trust_function_creators=1;
mysql> show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON    |
+---------------------------------+-------+


这样添加了参数以后，如果mysqld重启，那个参数又会消失，因此记得在my.cnf配置文件中添加：
log_bin_trust_function_creators=1






1    delete from t1 where 条件
2    delete t1 from t1 where 条件
3    delete t1 from t1,t2 where 条件
4    delete t1,t2 from t1,t2 where 条件




#update
mysql关联多表进行update更新操作
UPDATE Track
INNER JOIN MV
ON Track.trkid=MV.mvid
SET Track.is_show=MV.is_show
WHERE trkid<6


等同于


UPDATE Track,MV
SET Track.is_show=MV.is_show
WHERE Track.trkid=MV.mvid and trkid<6




mysqldump -h local -u root -p 123456 zxlh > zxlh.sql
mysqldump -uroot -d entrym> zxlh.sql 
mysqldump -uroot -p -d zxlh> zxlh.sql


scp /opt/zxlh.sql root@152.16.2.159:/opt/zxlh.sql


/usr/local/tomcat/webapps/Zheng/WEB-INF/log
scp /usr/local/tomcat/webapps/Zheng/WEB-INF/log/system.log root@172.16.2.159:/etc/system.log




mysqldump  -uroot -pzxtest135   test > /opt/test_1.sql


mysqldump  -uroot -p123456   zxlh > /db_bak/zxlh_0.sql


转换字符集，可以用gbk直接转换成utf-8
 iconv -f gbk -t utf-8  fy_nr3.txt>fy_nr4.txt
 
 
 【MySQL】[Err] [Imp] 2006 - MySQL server has gone away
 处理方法：
 /etc/my.cnf进行如下配置
 wait_timeout=2880000  
 interactive_timeout =2880000  
 max_allowed_packet=10M  
 
 #too many connections 连接数太多
 max_connections=1000  一般范围控制在500-1000
 
 
 #游标问题
 如果游标查询的结果集太大 大于内存 会导致执行效率很低
 可以考虑将查询的结果集分成多个子查询
 
#Innodb 表则可以通过执行以下语句来整理碎片，提高索引速度：
ALTER TABLE tbl_name ENGINE = InnoDB;
这其实是一个 NULL 操作，表面上看什么也不做，实际上重新整理碎片了。


linux启用MySQL慢查询
vim /etc/my.cnf
[mysqld]
slow-query-log = on # 开启慢查询功能
slow_query_log_file = /var/lib/mysql/slow-query.log # 慢查询日志存放路径与名称
long_query_time = 2 # 查询时间超过2s的查询语句
### log-queries-not-using-indexes = on # 列出没有使用索引的查询语句




#This function has none of DETERMINISTIC, NO SQL解决办法
mysql> show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
mysql> set global log_bin_trust_function_creators=1;
mysql> show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON    |
+---------------------------------+-------+


这样添加了参数以后，如果mysqld重启，那个参数又会消失，因此记得在my.cnf配置文件中添加：
log_bin_trust_function_creators=1


#强制走某个索引
select distinct '2015-07-30', t1.corp_id,t1.conn_id,t1.corp_name,t1.role
from corp_court_id t1 where t1.id>=i and t1.id<i+100
and not exists (select 1 from corp_court_id_bak t2  FORCE INDEX (idx_corp_court_idtest_id)
where t1.corp_id=t2.corp_id and t1.corp_name=t2.corp_name and t1.conn_id=t2.conn_id and t1.role=t2.role);


select * from table FORCE INDEX (idx_**) where id=**




#mysql数据库cpu过高超过100%
cpu 消耗过大通常情况下都是有慢sql 造成的，这里的慢sql 包括全表扫描，扫描数据量过大，内存排序，磁盘排序，锁争用等待等；




#设置事物为手动提交
SET GLOBAL init_connect='SET autocommit=0';


#监听端口
 tcpdump -i eth0 -nnA 'port 8080'
 
 #查看哪些表被锁
 show open TABLES where In_use > 0;
 
 #增加主键
 alter table tablename modify filedname int(11) auto_increment ,add primary key (filedname);
 
 #提交的两种方式 自动提交 手动提交
 
 #查看当前提交方式
 show variables like '%autocommit%'
 或者
 select @@autocommit
 
 设置提交方式
 set autocommit=1 --自动提交
 set autocommit=0 --手动提交
 
 
 查看硬盘 内存
 df -h    


free -m    按兆为单位输出内存的已用，未用，总共等结果


cat /proc/cpuinfo   输出各处理器的详细信息




MySql Host is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts' 解决方法
原因：
　　同一个ip在短时间内产生太多（超过mysql数据库max_connection_errors的最大值）中断的数据库连接而导致的阻塞；
解决方法：
1、提高允许的max_connection_errors数量（治标不治本）：
　   修改max_connection_errors的数量为1000： set global max_connect_errors = 1000;
　　 查看是否修改成功：show variables like '%max_connection_errors%';
 2.直接flush hosts;


 




 #mysql分区
 CREATE TABLE `corp_charge_3` (
  `id` int(11) NOT NULL DEFAULT '0' COMMENT '序号',
  `t_date` datetime DEFAULT NULL COMMENT '变动日期',
  `res_data` varchar(50) DEFAULT NULL COMMENT '数据来源',
  `justice` varchar(100) DEFAULT NULL COMMENT '法院',
  `case_no` varchar(200) DEFAULT NULL,
  `commit_date` varchar(20) DEFAULT NULL COMMENT '提交日期',
  `document` text,
  `title` varchar(500) DEFAULT NULL,
  `province` varchar(20) DEFAULT NULL,
  KEY `idx_pro` (`province`) USING HASH,
  key idx_id (id)
  #FULLTEXT KEY `idx_doc` (`document`)
) ENGINE=innodb DEFAULT CHARSET=utf8
PARTITION BY LIST  COLUMNS(province)
(PARTITION p0 VALUES IN ('浙江') ENGINE = innodb,
 PARTITION p1 VALUES IN ('河南') ENGINE = innodb) ;
 
 #索引禁用
 ALTER TABLE tbl2 DISABLE KEYS;
  #索引启用
 ALTER TABLE tbl2 enable KEYS;
 
 #Table 'xxx'is marked as crashed and last (automatic) repair failed 错误
service mysqld stop  停止mysql服务；
cd /var/local/mysql/tbl  切换到该表目录下(数据及索引文件目录)；
myisamchk -r xxx.MYI  （如果还是提示错误，就多加一个参数-f  强制进行。）
 
#找到mysqlcheck的路径执行修复 -r
 ./mysqlcheck -uroot -pzxtest246   -r  zxlh_import
 
 #中文显示乱码 修改字符集
 #查看字符集
 mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
 #修改字符集
mysql> set character_set_results=gb2312;
Query OK, 0 rows affected (0.00 sec)
#结果
mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
 
#如何查看bin-log，将二进制转成txt 即可以查看 
mysqlbinlog mysql-bin.000006 > mysql-bin.000006.txt  

#如果是row模式 查看
mysqlbinlog -vv mysql-bin.000006 > mysql-bin.000006.txt  

#row模式下增量还原方式

#批量删除进程
mysql -u root -e "show processlist" | grep -i "Locked" >> locked_log.txt


for line in `cat locked_log.txt | awk '{print $1}'`
do 
   echo "kill $line;" >> kill_thread_id.sql
done
现在kill_thread_id.sql的内容像这个样子
kill 66402982;
kill 66402983;
kill 66402986;
kill 66402991;
.....
好了, 我们在mysql的shell中执行, 就可以把所有锁表的进程杀死了.
mysql>source kill_thread_id.sql


#查看最后的死锁
show engine innodb status\G;


#重命名
rename table business_log  to business_log_bak20151224


#查找 0-1000行，输出到mysql。txt
cat  mysql-bin.000019.txt| head -n 1000 | tail -n +0 >>mysql.txt




1.跳过指定数量的事务：
mysql>slave stop;
mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1        #跳过一个事务
mysql>slave start

从库变为主库是需要执行

#进入information_schema 数据库（存放了其他的数据库的信息）
use information_schema;
#查询所有数据的大小：
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables;
#查看指定数据库的大小：
比如查看数据库zxlh的大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='zxlh';
#查看指定数据库的某个表的大小
比如查看数据库zxlh中 members 表的大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data,table_name from tables where table_schema='zxlh' and table_name='corp_charge';






mysql启动时出现以下错误：
[ERROR] /usr/local/mysql/libexec/mysqld: unknown option '--skip-federated'
[ERROR] Aborting
[Note] /usr/local/mysql/libexec/mysqld: Shutdown complete
只要将/etc/my.cnf文件中的skip-federated注释掉即可
通过查看/var/log/mysqld.log，可以看到错误信息




使用innodb_force_recovery解决MySQL崩溃无法重启问题

#查看表的状态包括引擎
SHOW TABLE STATUS FROM db_name WHERE NAME='table_name'; 
#修改表的引擎 
alter table table_name engine=myisam;  


#The total number of locks exceeds the lock table size错误
设置innodb_buffer_pool_size=1024M
默认是128M      
查看大小 show variables like '%innodb_buffer_pool_size%';




#找到对应点的sql语句放入mydb.sql
mysqlbinlog --start-position 106 --stop-position 220 mysql-bin.000013 >/tmp/mydb.sql
或者
#找到对应点的sql语句放入mydb.sql
mysqlbinlog --start-position 106 mysql-bin.000013 --stop-position 220 mysql-bin.000013 >/tmp/mydb.sql


#执行
mysql -uroot -p < /tmp/mydb.sql






两者之间的优缺点
共享表空间：
优点：
可以放表空间分成多个文件存放到各个磁盘上（表空间文件大小不受表大小的限制，如一个表可以分布在不同步的文件上）。数据和文件放在一起方便管理。
缺点：
所有的数据和索引存放到一个文件中以为着将有一个很常大的文件，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，这样对于一个表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析，日值系统这类应用最不适合用共享表空间。
独立表空间：
在配置文件（my.cnf）中设置： innodb_file_per_table
优点：
1．  每个表都有自已独立的表空间。
2．  每个表的数据和索引都会存在自已的表空间中。
3．  可以实现单表在不同的数据库中移动。
4．  空间可以回收（除drop table操作处，表空不能自已回收）
a)         Drop table操作自动回收表空间，如果对于统计分析或是日值表，删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。
b)         对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。
c)         对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。
缺点：
单表增加过大，如超过100个G。
 
相比较之下，使用独占表空间的效率以及性能会更高一点。




innodb_file_per_table=1 为使用独占表空间
innodb_file_per_table=0 为使用共享表空间




#充分利用CPU多核的处理能力
在Linux平台上就可以根据CPU核数来更改相应的参数值了，默认是4。


假如CPU是2颗8核的，那么可以设置：
innodb_read_io_threads = 8
innodb_write_io_threads = 8


如果数据库的读操作比写操作多，那么可以设置：
innodb_read_io_threads = 10
innodb_write_io_threads = 6


也就是说，你可以根据情况加以设置。
查看是否设置成功
show engine innodb status
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)




#立刻关机
shutdown -h now 立刻关机(root用户使用) 

#设置iptables

修改表名 重命名
rename table court_doc3 to court_doc2; 

进入相应的目录，排序
du -sh * |sort -nr  #查看目录下的文件大小并排序


临时打开general log
show global variables like "%genera%";

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

#打开日志
set global general_log=on;
#关闭日志
set global general_log=off;


#解压文件
 tar -zxvf /tmp/etc.tar.gz

tar -izxvf thursday_72.tar.gz


#服务无法重启时
尝试 删除进程及端口
ps -C mysqld
kill -9 pid
netstat -npl|grep 3306
kill -9 pid

#当发现init.d下面没有重启mysqld脚本时 可以使用如下方式重启服务
杀进程
pkill mysqld
起服务
/usr/pkg/bin/mysqld_safe &
#默认的user不写 就是mysql用户 但是有可能不是mysql 比如用户 sql
#会产生mysql.plugin.frm找不到异常，所以起服务最好带上 --user
 mysqld_safe --user=sql  &




#使用套接字进行登入socket
mysql -udba -S /var/lib/mysql/mysql.sock


查看某个日志文件中的关键字

grep 'aa'   test.log

根据大小清理日志使用 xargs传参数 truncate清理

find /home/souche/projects/jingwei-canal/bin/logs -name *.log -size +10G |xargs truncate -s0</i+100

当前目录下按文件大小排序

排序

du -sh *|sort -hr

linux创建快捷访问命令

vim .bash_profile

pwd='python3 /Users/dasouche/PycharmProjects/py_util.py pwd'

source   .bash_profile

然后在终端输入pwd 就可以出来一串字符串

31h@#ykbwi(nds=7l)zoe6$p2_%q

mac创建快捷访问命令

vim .zshrc

进入编辑窗口

pwd='python3 /Users/dasouche/PycharmProjects/py_util.py pwd'

source .zshrc

mysql设置权限过期，需要mysql5.7之后才生效

ALTER USER 'root'@‘localhost' PASSWORD EXPIRE INTERVAL 30 DAY;

ALTER USER 'dba'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

立即过期

ALTER USER 'dba'@'%' PASSWORD EXPIRE;

清空文件

cat /dev/null > test.txt

tar压缩及解压

压缩

tar -czvf /home/abc.tar.gz   /home/abc.txt

解压到当前文件夹

tar -zxvf abc.tar.gz