MySQL errors and usage Problems

In Linux, the following error is reported when you enter MySQL:

Can't connect to local MySQL server through socket '/var/lib/mysql. sock' (2)

This is very simple because the MySQL service is not started, but it cannot be seen from the literal meaning. It is quite strange. solution:

Service mysqld start

1. Where is the MySQL database data stored?

The datadir parameter in the/etc/mysql/my. cnf file specifies the location where the mysql database is stored. (Default value:/var/lib/mysql ).

2. view the mysql character set (encoding) command:

Show variables like 'character _ set _ % ';
Show variables like 'collation _ % ';
Show variables like '% server % ';
Status;

MySQL 4.1 Character Set Support has two aspects: Character set and Collation ). The support for character sets is refined to four levels: server, database, table, and connection ).

3. Unified encoding (character set) to utf8

When mysql is started, if a default character set is not specified, this value is inherited from the configuration file (the default mysql encoding is latin1). character_set_server is set to this default Character Set; when creating a new database, unless explicitly specified, the character set of this database is set to character_set_server by default; When a database is selected, character_set_database is set to the default Character Set of this database; when creating a table in this database, the default Character Set of the table is set to character_set_database, which is the default Character Set of this database. When setting a column in the table, unless explicitly specified, otherwise, the default character set in this column is the default Character Set of the table.

This problem arises, for example, a database is gbk encoded. If the character set is not specified when you access the database, it is gbk. This value will inherit the latin1 of the system, so that mysql Chinese garbled characters will be generated.

(1) The default mysql encoding is latin1. in/etc/mysql/my. cnf, set the default encoding to utf8:

Add default-character-set = utf8 under [client] and [mysqld. To make the settings take effect, run: sudo service mysql restart

(2) modify the php access method and add the following after calling the mysql_connect function:

Mysql_query ("set names 'utf8 '");
Set names 'utf8' is equivalent:
SET character_set_client = 'utf8'
SET character_set_connection = 'utf8'
SET character_set_results = 'utf8'

Note: set names must be connected to the database before the SQL statement is executed! What encoding does your database use? set names 'your encoding' before you operate on the database '.

4. Database encoding conversion (latin1 to utf8)

(1) latin1 encoding data export: mysqldump-uroot-p -- compatible = mysql40 -- default-character-set = latin1 mydb> ~ /Desktop/old. SQL

(2) create an empty Database: mynewdb

(3) Import utf8 encoded data: mysql-hlocalhost-uroot-p -- default-character-set = utf8 mynewdb <~ /Desktop/old. SQL

At this point, mynewdb is the database for utf8. The original mydb can be deleted, or step (2) can be omitted. Step (3) is directly exported back to mydb.

Note: This method is fatal when there are a large number of Chinese characters and other special characters in the data, it is very likely that in (3) the data cannot be imported due to an error, if the database is large, you can perform the preceding steps for each table.