Performance considerations for MYSQL source code installation under Linux systems

Performance considerations for source code installation:
17.3.1 Remove unnecessary modules:
Source code installation has greater flexibility because it can flexibly customize and compile the database. Certain compilation options can greatly enhance the performance of our database.
Execute the following command to see all compiled configuration options:
shell> ./configure --help
If you only install the client, you can execute the following command:
shell> ./configure --without-server
If you don’t want For log files and databases located under the "/usr/local/var" directory, use one of the following configure commands:
local/mysql/tmp/mysql.sock
17.3.2 Select only the character set to be used :
Change the default character set and collation after installation:
shell> ./configure -- with-charset=CHARSET
./configure --with-collation=COLLATION
Select the character set to be installed:
shell>./ configure --with-extra-charsets=LIST
list can be any of the following:
A series of character set names separated by spaces
complex -, to include all character sets that cannot be loaded dynamically
all -, to combine all character sets Included in binary
17.3.3 Compiled with pgcc:
pgcc 2.90.29 or newer:
CFLAGS="-O3 -mpentiumpro -mstack-align-double" CXX=gcc
CXXFLAGS="-O3 -mpentiumpro -mstack-align -double
-felide-constructors -fno- exceptions -fno-rtti"
17.3.4 Use static compilation to improve performance:
shell>./configure --with-client-ldflags=-all-static
--with- mysqld-ldflags=-all-static
17.4 mysql upgrade
Method 1 is the simplest and suitable for any storage engine (not necessarily the fastest)
Install a new database
Export the old database as text and import it into the new database
shell> mysqladmin -h hostname -P port -u user -p passwd create db_name
shell> mysqldump --opt db_name | mysql -h hostname -P port -u user -p
passwd db_name
Note: If the network is slow, you can export Add --compress to the option to reduce network transmission
Upgrade the privilege table
CP all the mysql database directories in the original library to overwrite the mysql database in the new library
Execute the mysql_fix_privilege_tables command in the shell to upgrade the permission table
shell>mysql_fix_privilege_tables
Restart the database Service
Method 2 is suitable for any storage engine and is faster
Install a new database
Create a directory to save the output file in the old library and back up the database:
shell> mkdir DUMPDIR
shell>mysqldump --tab=DUMPDIR db_name
Change the DUMPDIR directory Transfer the files in to the corresponding directory on the target machine and load the files into MySQL:
shell> mysqladmin create db_name # create database
shell> cat DUMPDIR/*.sql | mysql db_name # create tables in database
shell> mysqlimport db_name DUMPDIR /*.txt # load data into tables
(During the actual test, it was found that txt must be placed under data before execution, otherwise it will prompt that the file cannot be found)
Upgrade the permission table
Cp all the mysql database directories in the original library to overwrite For the mysql database in the new library, execute the mysql_fix_privilege_tables command in the shell to upgrade the privilege table
shell>mysql_fix_privilege_tables
Restart the database service
Method 3 is suitable for the myisam table and is the fastest
Install the new database
Copy all the files in the data directory in the original library ( .frm, .MYD, MYI) cp to the corresponding directory under the new library to upgrade the permission table
Cp all the mysql database directories in the original library to overwrite the mysql database in the new library. Execute the mysql_fix_privilege_tables command in the shell to upgrade the permission table
shell> mysql_fix_privilege_tables
flush tables or restart the database service to take effect

For more related articles, please pay attention to the PHP Chinese website (www.php.cn)!