Encoding of mysql database configuration on MAC

 

Recently, the mac osx lion system was installed, and the mysql 5.5.16 database was installed for development. However, we all know that the default mysql encoding is latin1. If you store Chinese characters, garbled characters may occur, then I went crazy about google, but it has never been solved. Most of the answers on the Internet are as follows:

1. copy any *. cnf file under/usr/local/mysql/support-files to/etc/my. cnf;

2. Add a default-character-set = utf8 (or default_character_set = utf8) after [mysqld] and [client] of the my. cnf file );

3. Save and exit;

4. Restart the mysql server.

However, I tried it many times. After the modification, the mysql service could not be started.

By chance, I found a way to solve the problem of configuring mysql encoding in linux. I made some modifications and found that mysql can be used, therefore, this blog post hopes that friends who install mac do not need to take a detour. The configuration method is as follows:

1. copy any *. cnf file under/usr/local/mysql/support-files to/etc/my. cnf;

2. in my. add a default-character-set = utf8 (not default_character_set = utf8 [I] [/I] after the [client] of the cnf file. I have not tried this configuration, I don't know if it will succeed.) The key here is the configuration. Add the following three sentences after [mysqld:

Default-storage-engine = INNODB

Character-set-server = utf8

Collation-server = utf8_general_ci;

3. Save and exit;

4. Restart the mysql server.

Then, check the encoding in the mysql database and change it to utf8.

The following figure shows how to restart the mysql server and access the mysql database:

Java code

Welcome to the MySQL monitor. Commands end with; or \ g.

Your MySQL connection id is 520

Server version: 5.5.16 MySQL Community Server (GPL)

 

Copyright (c) 2000,201 1, Oracle and/or its affiliates. All rights reserved.

 

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

 

Type 'help; 'or' \ H' for help. type' \ C' to clear the current input statement.

 

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 | utf8 |

| Character_set_system | utf8 |

| Character_sets_dir |/usr/local/mysql-5.5.16-osx10.6-x86_64/share/charsets/|

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

8 rows in set (0.00 sec)

 

Welcome to the MySQL monitor. Commands end with; or \ g.

Your MySQL connection id is 520

Server version: 5.5.16 MySQL Community Server (GPL)

 

Copyright (c) 2000,201 1, Oracle and/or its affiliates. All rights reserved.

 

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

 

Type 'help; 'or' \ H' for help. type' \ C' to clear the current input statement.

 

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 | utf8 |

| Character_set_system | utf8 |

| Character_sets_dir |/usr/local/mysql-5.5.16-osx10.6-x86_64/share/charsets/|

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

8 rows in set (0.00 sec)

 

So far, this problem that has been plagued for many days has finally been solved.

The modified content of my. cnf file is attached. If you do not know why the attachment cannot be uploaded, paste the Code:

Java code

# Example MySQL config file for small systems.

#

# This is for a system with little memory (<= 64 M) where MySQL is only used

# From time to time and it's important that the mysqld daemon

# Doesn' t use much resources.

#

# MySQL programs look for option files in a set

# Locations which depend on the deployment platform.

# You can copy this option file to one of those

# Locations. For information about these locations, see:

# Http://dev.mysql.com/doc/mysql/en/option-files.html

#

# In this file, you can use all long options that a program supports.

# If you want to know which options a program supports, run the program

# With the "-- help" option.

 

# The following options will be passed to all MySQL clients

[Client]

Default-character-set = utf8

# Password = your_password

Port = 3306

Socket =/tmp/mysql. sock

 

# Here follows entries for some specific programs

 

# The MySQL server

[Mysqld]

Default-storage-engine = INNODB

Character-set-server = utf8

Collation-server = utf8_general_ci

Port = 3306

Socket =/tmp/mysql. sock

Skip-external-locking

Key_buffer_size = 16 K

Max_allowed_packet = 1 M

Table_open_cache = 4

Sort_buffer_size = 64 K

Read_buffer_size = 256 K

Read_rnd_buffer_size = 256 K

Net_buffer_length = 2 K

Thread_stack = 128 K

 

# Don't listen on a TCP/IP port at all. This can be a security enhancement,

# If all processes that need to connect to mysqld run on the same host.

# All interaction with mysqld must be made via Unix sockets or named pipes.

# Note that using this option without enabling named pipes on Windows

# (Using the "enable-named-pipe" option) will render mysqld useless!

#

# Skip-networking

Server-id = 1

 

# Uncomment the following if you want to log updates

# Log-bin = mysql-bin

 

# Binary logging format-mixed recommended

# Binlog_format = mixed

 

# Causes updates to non-transactional engines using statement format to be

# Written directly to binary log. Before using this option make sure that

# There are no dependencies between transactional and non-transactional

# Tables such as in the statement insert into t_myisam SELECT * FROM

# T_innodb; otherwise, slaves may diverge from the master.

# Binlog_direct_non_transactional_updates = TRUE

 

# Uncomment the following if you are using InnoDB tables

# Innodb_data_home_dir =/usr/local/mysql/data

# Innodb_data_file_path = ibdata1: 10 M: autoextend

# Innodb_log_group_home_dir =/usr/local/mysql/data

# You can set .. _ buffer_pool_size up to 50-80%

# Of RAM but beware of setting memory usage too high

# Innodb_buffer_pool_size = 16 M

# Innodb_additional_mem_pool_size = 2 M

# Set .. _ log_file_size to 25% of buffer pool size

# Innodb_log_file_size = 5 M

# Innodb_log_buffer_size = 8 M

# Innodb_flush_log_at_trx_commit = 1

# Innodb_lock_wait_timeout = 50

 

[Mysqldump]

Quick

Max_allowed_packet = 16 M

 

[Mysql]

No-auto-rehash

# Remove the next comment character if you are not familiar with SQL

# Safe-updates

 

[Myisamchk]

Key_buffer_size = 8 M

Sort_buffer_size = 8 M

 

[Mysqlhotcopy]

Interactive-timeout

 

Author: zhyl2010