MySQL使用知识点和故障解决

1. 关闭autocommit

mysql的autocommit（自动提交）默认是开启，其对mysql的性能有一定影响，举个例子来说，如果你插入了1000条数据，mysql会commit1000次的，如果我们把autocommit关闭掉，通过程序来控制，只要一次commit就可以了。[1]

2. 时间

 (1) Invalid DATE, DATETIME, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00' or '0000-00-00 00:00:00').

3. mysql导入导出数据库方法[3]

(1)导出

mysqldump --opt -u [uname] -p[pass] [dbname] > [backupfile.sql]

• [uname] Your database username
• [pass] The password for your database (note there is no space between -p and the password)
• [dbname] The name of your database
• [backupfile.sql] The filename for your database backup
• [--opt] The mysqldump option

(2) 导入

i. Create an appropriately named database on the target machine

ii. Load the file using the mysql command:

 mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]

4. mysql 恢复初始配置, 解决Mysql.sock is missing问题

dpkg -l | grep mysql-server 先查看版本

sudo dpkg-reconfigure mysql-server-5.5 后重新配置

参考[2]

5. MySQL 5.5 Database directory change on Ubuntu 12.04 LTS

转至[4], 当时由于目标硬盘是外接的且是ntfs格式, 所以没有成功

Once the MySQL server installed on Ubuntu server, the default database directory location is /var/lib/mysql. If you want to change the default database directory (for example to /mnt/data/db/mysql or any other directory of your choice) - just follow steps below:

• Stop MySQL server:

sudo service mysql stop

• I have completed the instructions below as root user - Create the new database directory:

mkdir /mnt/data/db/mysql

• Modify the MySQL configuration file to point to the new database directory:

vi /etc/mysql/my.cnf 
#and change to: datadir = /mnt/data/db/mysql

•  Change usr.sbin.mysqld file to reflect the new database directory:

vi /etc/apparmor.d/usr.sbin.mysqld
#and change from
/var/lib/mysql/ r, 
/var/lib/mysql/** rwk, 
to
/mnt/data/db/mysql/ r, 
/mnt/data/db/mysql/** rwk,
#and run
/etc/init.d/apparmor restart 

• Initialize the new database directory:

mysql_install_db --user=mysql --datadir=/mnt/data/db/mysql
#change permissions as necessary 
chown -R mysql:mysql /mnt/data/db/mysql 
chmod -R 700 /mnt/data/db/mysql 

• Start MySQL server: 

sudo service mysql start

It is done! 
You can change the new database directory to any directory of your choice, just modify the commands above to reflect it.
If you get "Access denied for user 'root'@'localhost'" after changes above, then run the following to update MySQL root user password: 
sudo dpkg-reconfigure mysql-server-5.5

6. Mysql替换某个字段

update image_map set new_path = replace (new_path,'image2','image_transform1') where image_seqid <=29145268;

Reference

[1] Mysql事务处理

[2] Mysql.sock is missing buntu 12.4 LTS

[3] How to Back Up and Restore a MySQL Database

[4] MySQL 5.5 Database directory change on Ubuntu 12.04 LTS