CentOS MySQL 5.1.x upgrade to MySQL 5.5.x tutorial

When you invoke a micro-interface, if you encounter a nickname or message that contains an expression, you will see an error similar to incorrect string value: ' \xf0\x8f ' for column ' XXX ' when you write the data to MySQL. The reason is that the UTF-8 encoding may be two, three, or four bytes, and the expression above is emoji, which is common in the iphone, and many are four bytes. However, MySQL 5.1.x does not support such four-byte utf-8 encoding, only 5.5.x to support, so only MySQL can be upgraded to 5.5.x.

1. Install MySQL
Installing MySQL 5.5.x under Windows is very simple and is no longer detailed here. Linux below because from the MySQL 5.5 to start using CMake to do config, so with the previous version of the installation method is different, here explains the different places.

First install the Base Dependency library:

Lang=c
yum-y Install gcc-c++ gperf ncurses-devel readline-devel libaio-devel

Then download the compile installation CMake:

wget http://www.cmake.org/files/v2.8/cmake-2.8.12.2.tar.gz
tar xzvf cmake-2.8.12.2.tar.gz
CD cmake-2.8.12.2
./configure && make && make install

After the installation is complete, you can perform the cmake-version command to see if the CMake is properly installed.

After the basic work is done, you can download and install MySQL, here take mysql-5.5.36 as an example:

wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.36.tar.gz
tar xzvf mysql-5.5.36.tar.gz
CD mysql-5.5.36
cmake-dcmake_install_prefix=/usr/local/mysql-dwith_innobase_storage_engine=1-dwith_readline=1 -dwith_ssl=system-dwith_zlib=yes-denabled_local_infile=1-ddefault_charset=utf8-ddefault_collation=utf8_general _ci-dextra_charsets=all make
&& make install

The CMake installation options are available in a detailed view of the MySQL source-configuration options, and by default the MyISAM, MERGE, memory, and CSV engine default configurations are followed, so use only with_ The innobase_storage_engine=1 configuration installs the InnoDB engine.

If the CentOS version is below 6.x, the following error may occur during installation:

/root/downloads/mysql-5.5.32/vio/viossl.c:in function ' Ssl_do ':
/root/downloads/mysql-5.5.32/vio/viossl.c:175: Error: ' Ssl_op_no_compression ' not declared (first use in this function)
/root/downloads/mysql-5.5.32/vio/viossl.c:175: Error: (even if multiple occurrences occur within a function, each undeclared identifier in its
/ROOT/DOWNLOADS/MYSQL-5.5.32/VIO/VIOSSL.C:175: Error: Only one report is reported within the function. )
MAKE[2]: * * * [VIO/CMAKEFILES/VIO.DIR/VIOSSL.C.O] Error 1
MAKE[1]: * * * [Vio/cmakefiles/vio.dir/all] Error 2
Make: * * * [ALL] Error 2
This is a bug in the MySQL 5.5.32 because it cannot be compiled in openssl-1.0.0 and later, and if SSL is not required to remove WITH_SSL configuration entries, you can also upgrade OpenSSL versions. Another convenient way to make a patch before CMake:

wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.36.tar.gz
tar xzvf mysql-5.5.36.tar.gz
CD mysql-5.5.36
wget-c "http://bugs.mysql.com/file.php?id=19941&bug_id=68999"-O mysql-openssl.patch
Patch-p1 < Mysql-openssl.patch

Then proceed to the above CMake operation.

Finally initialize the database to ensure that the MySQL user is created, and if not, use the following command to create

Groupadd MySQL
useradd-s/sbin/nologin-m-g MySQL MySQL

Set directory permissions and install the database

Cd/usr/local/mysql
chown-r mysql.
chgrp-r MySQL.

# Install the database, where the script location is different from 5.1.x

scripts/mysql_install_db--user=mysql
chown-r root.
chown-r MySQL Data

If it is not upgraded, you can use the MySQL default profile first, and then start MySQL:

# The following file is placed in the Support-files directory under the
support-files/my-medium.cnf/etc/my.cnf CP
Support-files/mysql.server /etc/init.d/mysql
chmod +x/etc/init.d/mysql
/etc/init.d/mysql start
# Modify
root password
bin/  Mysqladmin-uroot Password 123
history-c
/etc/init.d/mysql Restart
# boot up
cat >>/etc/rc.local </etc/init.d/mysql start
> EOF

2. Modify the MySQL 5.1.x configuration file

If you are upgrading, you need to make some changes before using the original configuration file, and the following configuration items are commonly used:

Default-character-set has been modified to character-set-server, encoding has been specified during Linux installation, so there is no need to set in the configuration file, but Windows needs to be set
Innodb_file_io_threads has been discarded and modified into innodb_read_io_threads and innodb_write_io_threads.
Skip-locking has been discarded and modified into skip-external-locking.
Log-long-format has been discarded and modified into Log-short-format.
Log_slow_queries has been deprecated, modified to slow-query-log, and this parameter is set to 1 enable slow query logging, 0 is off, use Slow_query_log_file to specify the name of the log file, the default file name is Host_ Name-slow.log
Lower_case_table_name has been deprecated and proposed to be modified into lower_case_table_names
Alternatively, you can change the previous built-in file format antelope to the new Barracuda format:
Innodb_file_format=barracuda
Linux to be consistent with Windows, set table name or field name is not sensitive to case:
Lower_case_table_names=1

3. Original database repair and upgrade

To ensure the integrity of the data, after importing the old data into the new database (for ease of management, you can merge the old MySQL database into the new database and then MySQL server for it to take effect), you need to upgrade the repair database using the Mysql_upgrade command. Otherwise, the cannot load from Mysql.proc may appear. The table is probably corrupted error. Execute the following command in the MySQL bin directory (parameters and their meanings see Mysql_upgrade-check and upgrade MySQL Tables):

Mysql_upgrade-p3300-uroot-p--force
Make sure that the MySQL password is entered correctly, otherwise there will be a FATAL Error:upgrade failed error.

It should be noted that the following error occurs if you execute the above command in a Windows environment:

Looking for ' Mysql.exe ' as:c:\mysql\bin\mysql.exe
FATAL error:can ' t execute ' c:\mysql\bin\mysql.exe '

If you execute the MySQL command there will be "unknown variable ' Character-set-server=utf8" error, this is a MySQL bug, You can first change the character-set-server in the MySQL profile My.ini to the old Default-character-set, and then execute the above command, make sure that the MySQL service is up and running, and wait until the upgrade is complete and then put the D in the configuration file Efault-character-set is replaced with the new character-set-server.

The final solution to the first-mentioned problem is to upgrade the corresponding field encoding in the table from Utf-8 to UTF8MB4:

ALTER TABLE ' table_name ' change ' colum_name ' colum_name ' VARCHAR (255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NU LL;