mysql日常记录

安装了mysql5.7之后初始密码不再默认为空

1.查看初始密码：

[root@VM_225_102_centos ~]# grep 'temporary password' /var/log/mysqld.log 
2016-07-08T02:25:46.311098Z 1 [Note] A temporary password is generated for root@localhost: MtPqF0/oN5zo

即初始密码为 MtPqF0/oN5zo    （密码是随机产生的，每台机器产生的都不一样哦）

或者

另外一种方法查看默认密码：

[root@localhost src]# cat /root/.mysql_secret

# The random password set for the root userat Fri Jan 10 20:00:34 2014 (local time): aJqZsA2m

这里的aJqZsA2m就是生成的root随机密码啦

 You must reset your password using ALTER USER statement before executing this statement

安装完mysql 之后，登陆以后，不管运行任何命令，总是提示这个

step 1: SET PASSWORD = PASSWORD('your new password');

step 2: ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;

step 3: flush privileges;

或者

mysql> alter user root@localhost identified by 'tyzZ001!';
Query OK, 0 rows affected (0.00 sec)

新修改的密码中 必须包含 大小写字母数字及符号

或者

更改密码： update mysql.user set authentication_string=password('123qwe') where user='root' and Host = 'localhost';

*特别提醒注意的一点是，新版的mysql数据库下的user表中已经没有Password字段了

而是将加密后的用户密码存储于authentication_string字段

Your password does not satisfy the current policy requirements

set global validate_password_policy=0;  #设置策略为最低的。

set global validate_password_length=4;  #设置密码长度最少4位。

之后运行上边的修改密码的语句即可。

导出文件报错：The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

连接数据库，并查看：show variables like 'secure_file_priv';

如果secure_file_pri没有值，则需要修改配置文件。有的话，直接就可以导出到相应的目录下

vim /etc/my.cnf

secure_file_priv=/tmp/

重新导出：select * from alifeba_user into outfile '/var/lib/mysql-files/1.txt';

导入数据：load data infile '/var/lib/mysql-files/1.txt' into table alifeba_user1;

表之间导数据

1.INSERT INTO SELECT语句

      语句形式为：Insert into Table2(field1,field2,...) select value1,value2,... from Table1

      要求目标表Table2必须存在，由于目标表Table2已经存在，所以我们除了插入源表Table1的字段外，还可以插入常量。示例如下：

2.SELECT INTO FROM语句

      语句形式为：SELECT vale1, value2 into Table2 from Table1

      要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中。

##当无法导入或者权限有问题时，尝试使用这种方式进行导入

mysql -uroot -p --local-infile

load data local infile '/home/lsanotes/newsdata.sql'  into table news　character set utf8;

导csv文件

通过mysql客户端shell连接到服务器，选择使用的数据库，输入sql代码： 

select * from test_info   
into outfile'/tmp/test.csv'   
fields terminated by ',' optionally enclosed by '"' escaped by'"'   
lines terminated by '\r\n'; 

里面最关键的部分就是格式参数

这个参数是根据RFC4180文档设置的，该文档全称Common Format andMIME Type for Comma-Separated Values (CSV)Files，其中详细描述了CSV格式，其要点包括：
(1)字段之间以逗号分隔，数据行之间以\r\n分隔；

(2)字符串以半角双引号包围，字符串本身的双引号用两个双引号表示。

通过执行上述代码，便可以将需要的数据以csv格式导出到执行的文件中。

 

另外，MySQL中导入CSV格式数据的sql代码如下:

load data infile'/tmp/test.csv'  
into tabletest_info   
fields terminated by ','  optionally enclosed by'"' escaped by '"'  
lines terminated by '\r\n';

##部分字段导出导入

select Host,Password from mysql.user   
into outfile '/tmp/test3.csv'   
fields terminated by ',' optionally enclosed by '"' escaped by '"'   
lines terminated by '\r\n'



load data infile '/tmp/test3.csv'  
into table mysql.t2   
fields terminated by ','  optionally enclosed by '"' escaped by '"'  
lines terminated by '\r\n'(Host,user) ;

##导出xls文件

mysql -h127.0.0.1 -uroot -p123 -e "select * fromtest" test > /root/test.xls

###skip-networking和bind-address

my.cnf

#skip-networking
bind-address = 127.0.0.1

此项设置允许 tcp/ip 访问，但是只有127.0.0.1 有可以访问,如果把skip-networking 开启。则只能通过sock连接。
顺便提下，

mysql -h localhost -u eric -p saker

mysql -h 127.0.0.1 -u eric -p saker

 

这两个命令在skip-networking 开启的时候localhost能正常登录mysql,但是127.0.0.1不能。具体原因如下：

大多数程序将主机名localhost和IP地址127.0.0.1 视作“本地服务器”的同义词。

 

但在UNIX系统中，MySQL 有所不同：
出现localhost时，MySQL会尝试使用一个Unix domain socket 文件来连接本地服务器。

要强制使用TCP/IP连接到本地服务器，那就使用IP地址 127.0.0.1 而不是主机名localhost。
可以通过指定 --protocol=tcp 选项来强制使用TCP/IP进行连接

log_bin_trust_function_creators变量解释

　在MySQL主从复制机器的master的数据库中创建function，报出如下错误：
　　Error Code: 1418. 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)
　　原来是因为在主从复制的两台MySQL服务器中开启了二进制日志选项log-bin,slave会从master复制数据，而一些操作，比如function所得的结果在master和slave上可能不同，所以存在潜在的安全隐患。因此，在默认情况下回阻止function的创建。
　　有两种办法来解决这一问题
1.将log_bin_trust_function_creators参数设置为ON，这样一来开启了log-bin的MySQL Server便可以随意创建function。这里存在潜在的数据安全问题，除非明确的知道创建的function在master和slave上的行为完全一致。
　　设置该参数可以用动态的方式或者指定该参数来启动数据库服务器或者修改配置文件后重启服务器。需注意的是，动态设置的方式会在服务器重启后失效。
mysql> show variables like 'log_bin_trust_function_creators';
 mysql> set global log_bin_trust_function_creators=1;
　　另外如果是在master上创建函数，想通过主从复制的方式将函数复制到slave上则也需在开启了log-bin的slave中设置上述变量的值为ON（变量的设置不会从master复制到slave上，这点需要注意），否则主从复制会报错。

2.明确指明函数的类型
1 DETERMINISTIC 不确定的
2 NO SQL 没有SQl语句，当然也不会修改数据
3 READS SQL DATA 只是读取数据，当然也不会修改数据
　　比如：CREATE DEFINER=`username`@`%` READS SQL DATA FUNCTION `fn_getitemclock`(i_itemid bigint,i_clock int,i_pos int) RETURNS int(11)...
这样一来相当于明确的告知MySQL服务器这个函数不会修改数据，因此可以在开启了log-bin的服务器上安全的创建并被复制到开启了log-bin的slave上。

##mysql报错：/usr/local/mysql/libexec/mysqld: Incorrect key file for table '/dev/shm/#sql_ce9_0.MYI'; try to repair it

问题原因：在执行SQL的时候，产生临时数据占满了/tmp磁盘空间，可以查看mysql临时目录的空间

show variables like "tmpdir";  ##查看下临时目录，然后去看看系统下分区是否还有空间。注意这个目录在查询的时候才可能被占满，可能需要开发查询的时候在查看。watch -n 1 df -lh   命令观察下。

修改的方式： 修改配置文件，改变临时文件路径。之后重启数据库即可（这个必须重启），或者扩容临时目录（得能扩容才可以）

##mysql报错：170930  9:47:42  InnoDB: ERROR: the age of the last checkpoint is 9433772,
InnoDB: which exceeds the log group capacity 9433498.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.

问题原因：ib_logfile*日志文件配置的过小，导致不够用。

修改方式：

刷表锁表：flush tables with read lock;

暂停mysql, service mysqld stop
转移ib_logfile0和ib_logfile1,mv /var/lib/mysql/ib_logfile* /tmp
编辑my.cnf , 增加 innodb_log_file_size=256M
启动mysql, service mysqld start

##mysql报错：[Warning] Aborted connection 305627 to db: 'db' user: 'dbuser' host: 'hostname' (Got an error reading communication packets

参看：https://www.percona.com/blog/2016/05/16/mysql-got-an-error-reading-communication-packet-errors/

可能的原因：

• 客户端连接成功但不正确地关闭（可能与不正确关闭连接有关）
• 客户端睡眠时间长于定义的wait_timeout或interactive_timeout秒（最终导致连接进入wait_timeout秒钟，然后连接被MySQL服务器强行关闭）
• 客户端异常终止或超出 查询的  max_allowed_pa​​cket