Brief introduction to a data migration

After several days of data migration, the database and table encoding were not set because of the Library's disgusting design.

Ah ~ It is not a good thing to wipe your ass.

# Install MySQL

# If MySQL cannot import gb2312 encoded data, it may be because the gb2312 option is not specified during./configure compilation during installation,

# See http://blog.chinaunix.net/uid-20494084-id-3232230.html

[[email protected] ~]# ./configure  --prefix=/usr/local/mysql/   --with-extra-charsets=all   --with-charset=gb2312  --with-charset=gbk  --with-charset=utf8

# Cold backup and cold recovery

# If the database is very large, you can consider copying the database file (directory) # before cold backup, it is best to disable the MySQL service [[email protected] ~] # Service mysqld stop or [[email protected] ~] #/Usr/local/MySQL/support-files/MySQL. server stop # store the database in $ mysql/data or my. CNF datadir specified position # After tar compression, the library size can be reduced a lot [[email protected] ~] # Cp-r analysedb/usr/local/MySQL/data # After the copy is complete, you need to modify the database file owner. Otherwise, MySQL reports a permission error [[email protected] ~] # Chown-r mysql. MySQL/usr/local/MySQL/data/analysedb

# Specify encoding when creating a database

Mysql> create database if not exists 'yourdb' default charset utf8 collate utf8_general_ci; or mysql> Create Database 'yourdb' default Character Set GBK collate gbk_chinese_ci;

# -- Hex-blob

# -- Default-character-set = GBK if your library encoding is inconsistent with that of MySQL, you 'd better specify

#-B import and export structure. If the encoding of MySQL and the database is inconsistent, it is recommended to specify the encoding on the create database statement.

[[email protected] ~]# ./mysqldump -uroot -p123456 -B --hex-blob --default-character-set=gbk analysedb > /tmp/analysedb.gbk.sql

# If the encoding of the tables in the database is inconsistent, it is best to export the tables by encoding.

[[Email protected] ~] #. /Mysqldump-uroot-p123456 -- Hex-blob -- default-character-set = GBK analysedb t_user t_monitor>/tmp/analysedb. part. GBK. SQL or [[email protected] ~] #./Mysqldump-uroot-p123456 -- Hex-blob -- default-character-set = utf8 analysedb t_user t_monitor>/tmp/analysedb. Part. utf8. SQL

# My. CNF Configuration

# Max_allowed_packet: If you want to import a large amount of data, you 'd better modify this option.

# Character-set-Server = utf8: If this item is not specified, the default MySQL Character Set uses Latin1 (that is, the iso8859-1)

[client]default-character-set=utf8port = 3307socket = /var/run/mysqld/mysql-slave.sock[mysqld]character-set-server=utf8port = 3307datadir = /data/3307/datasocket = /var/run/mysqld/mysql-slave.sockskip-external-lockingkey_buffer_size = 16Mmax_allowed_packet = 16Mtable_open_cache = 64sort_buffer_size = 512Knet_buffer_length = 8Kread_buffer_size = 256Kread_rnd_buffer_size = 512Kmyisam_sort_buffer_size = 8Mmax_connections=5000server_id=100log-bin=mysql-bin-100binlog-ignore-db=mysqlbinlog-ignore-db = testbinlog-ignore-db = information_schemabinlog-ignore-db = performance_schemarelay_log_recovery = 1binlog_format=mixedskip-name-resolveskip-external-lockinglog_slave_updates = 1sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES[mysql]default-character-set=utf8no-auto-rehash

# Display table creation structure information

mysql> SHOW CREATE TABLE `analysedb`.`t_user`;

# Modify Table Information

mysql> ALTER TABLE `analysedb`.`t_user` DEFAULT CHARSET=utf8;

# Modifying Field Information

mysql> ALTER TABLE `analysedb`.`t_user`  CHANGE COLUMN `username` `username` varchar(128) CHARACTER SET utf8 NULL DEFAULT NULL;

# Display the table information of a specified database

mysql> use information_schema;mysql> select table_name from tables where table_schema=‘analysedb‘ order by table_collation,table_name;

# Import data

mysql> source /tmp/analysedb.part.utf8.sqlmysql> source /tmp/analysedb.part.gbk.sql

Brief introduction to a data migration