MySQL All in One 长期更新

个人笔记，用于MySQL数据库的安装、使用、问题追踪、以及经验总结

安装问题

  安装5.7.20版本遇到的问题。

安装时说要装很多依赖，又来装了一大堆，最后其它的都装成功了，只剩下Mysql srever怎么装也不行，一只提示如下错误。

Action 2:54:32: INSTALL. 
1: MySQL Server 5.7 2: {81B27388-3733-4B65-8D84-AD9C9113B498} 
Action 2:54:32: FindRelatedProducts. Searching for related applications
Action 2:54:32: AppSearch. Searching for installed applications
Action 2:54:32: LaunchConditions. Evaluating launch conditions
This application requires Visual Studio 2013 Redistributable. Please install the Redistributable then run this installer again.
1: MySQL Server 5.7 2: {81B27388-3733-4B65-8D84-AD9C9113B498} 3: 3 

意思是缺少Visual Studio 2013 Redistributable，因为本人是64位系统，就去微软下载了64位的文件来安装，最后还是不行，后来改为使用32位文件，一次性成功。一下是32位Visual Studio 2013 Redistributable下载链接：

Visual Studio 2013 Redistributable

希望后来的同胞少走弯路。

---

Backup Restore

mysqldump -u root -p database_nane > file_name.sql;

mysqlimport -u root -p database_name < file_name.sql;

mysql> use db_name;
mysql> source backup-file.sql;

mysql -p -u[user] [database] < backup-file.sql

---

Reset Password

with root password

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

without root password

sudo service mysqld stop
sudo mysqld_safe --skip-grant-tables &
mysql -u root
use mysql;
update user set password=password("root") where user='root';
flush privileges;
exit
service mysqld start

---

change MySQL default installation encoding

System: CentOS

vi /etc/my.cnf
#add server-character-set=utf8 under [mysqlserver]
#add default-character-set=utf8 under [mysql]
#restart mysql
service mysqld restart
#check change:
#1.login mysql
mysql -u root -p
#2.write script
show variables like 'character%';

MySql内置方法

GROUP_CONCAT