Shulou(Shulou.com)06/01 Report--

. Database 5.5.32 compilation and installation and simple preliminary optimization 1. Environment:

[root@mysql-master01 sbin] # cat/etc/redhat-release

CentOS release 6.6 (Final)

Ip: public network: 10.0.0.31 intranet: 172.16.1.31

two。 Installation steps:

Useradd mysql-s / sbin/nologin-M

Id mysql

Yum install cmake-y

Yum install libaio-develncurses-devel-y

Rpm-qalibaio-develncurses-devel

Mkdir / server/soft-p

Cd / server/soft/

Rz-y

Ls

Tarxf mysql-5.5.32.tar.gz

Ls

Cd mysql-5.5.32

Cmake. -DCMAKE_INSTALL_PREFIX=/aliyun/mysql-5.5.32-DMYSQL_DATADIR=/aliyun/mysql-5.5.32/data-DMYSQL_UNIX_ADDR=/aliyun/mysql-5.5.32/tmp/mysql.sock-DDEFAULT_CHARSET=utf8-DDEFAULT_COLLATION=utf8_general_ci-DEXTRA_CHARSETS=gbk,gb2312,utf8 Ascii-DENABLED_LOCAL_INFILE=ON-DWITH_INNOBASE_STORAGE_ENGINE=1-DWITH_FEDERATED_STORAGE_ENGINE=1-DWITH_BLACKHOLE_STORAGE_ENGINE=1-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1-DWITHOUT_PARTITION_STORAGE_ENGINE=1-DWITH_FAST_MUTEXES=1-DWITH_ZLIB=bundled-DENABLED_LOCAL_INFILE=1-DWITH_READLINE=1-DWITH_EMBEDDED_SERVER=1-DWITH_DEBUG=0

Make

Make install

Echo $?

Ln-s / aliyun/mysql-5.5.32/ / aliyun/mysql

Ll / aliyun/mysql

/ aliyun/mysql/scripts/mysql_install_db--basedir=/aliyun/mysql-datadir=/aliyun/mysql/data/-user=mysql

Ll / aliyun/mysql

Chown-R mysql.mysql / aliyun/mysql/

\ cp / aliyun/mysql/support-files/my-small.cnf/etc/my.cnf

Sed-I 's#/usr/local/mysql#/aliyun/mysql#g'/aliyun/mysql/bin/mysqld_safe

Sed-I 's#/usr/local/mysql#/aliyun/mysql#g'/aliyun/mysql/support-files/mysql.server

\ cp / aliyun/mysql/support-files/mysql.server / etc/init.d/mysqld

3. Start the MySQL database

/ etc/init.d/mysqld start

/ etc/init.d/mysqld stop

Chkconfigmysqld on

Ln-s / aliyun/mysql/bin/*/usr/local/sbin

Or:

Cp / aliyun/mysql/bin/*/usr/local/sbin/

Ll / usr/local/sbin/

/ etc/init.d/mysqld start

Mysql

II. MySQL 5.6.16 compilation and installation

Environment:

[root@mysql-5 soft] # ifconfig eth0 | awk-F'[:] + 'NR==2 {print $4}'; ifconfig eth2 | awk-F'[:] + 'NR==2 {print $4}'

192.168.56.41 external network

172.16.1.41 Intranet

[root@mysql-5 soft] # cat / etc/redhat-release

CentOS release 6.5 (Final)

/ etc/init.d/iptables stop

Or:

Vim / etc/sysconfig/iptable

-An INPUT-m state-- state NEW-m tcp- p tcp--dport 3306-j ACCEPT

Grep-I "SELINUX=disabled" / etc/sysconfig/selinux

1. Download yum Feed

Wget-O / etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-6.repo

Yum install gccgcc-c++ gcc-g77autoconf automake make cmake bison make perl perl-devel ncurses ncurses-devel

two。 Create a software installation directory and extract the software, etc.

[root@mysql-5 yum.repos.d] # cd / data/soft/

[root@mysql-5 soft] # ls

Cmake-2.8.4.tar.gz mysql-5.6.16.tar.gz

Tar xf mysql-5.6.16.tar.gz-C / aliyun/server/

Tar xf cmake-2.8.4.tar.gz-C / aliyun/server/

75pxake compilation and installation

Cd / aliyun/server/

Ls

Cd cmake-2.8.4/

. / configure--prefix=/aliyun/server/cmake

Gmake

Make install

Echo $?

Vim / etc/profile

PATH=/aliyun/server/cmake/bin:$PATH

Export path

Echo "PATH=/aliyun/server/cmake/bin:$PATH" > > / etc/profile

Source / etc/profile

Cmake-version

[root@mysql-5 scripts] # cmake-- version

Cmake version 2.8.4

4. Preparation before compiling and installing MySQL5.6

Groupadd mysql

Useradd-g mysql-s'/ sbin/nologin' mysql

Cd / aliyun/server/

Mkdir-p / data/mysql/data

Chown mysql.mysql-R / data/mysql/data/

Cd / aliyun/server/mysql-5.6.16/

5. Compile and install MySQL database 5.1MySQL5.6.16 compile and install successfully:

Cmake.-DCMAKE_INSTALL_PREFIX=/aliyun/server/mysql-5.6.16-DMYSQL_DATADIR=/data/mysql/data-DDEFAULT_CHARSET=utf8-DSYSCONFDIR=/etc-DDEFAULT_COLLATION=utf8_general_ci-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii-DENABLED_LOCAL_INFILE=ON-DWITH_INNOBASE_STORAGE_ENGINE=1-DWITH_FEDERATED_STORAGE_ENGINE=1-DWITH_BLACKHOLE_STORAGE_ENGINE=1-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1-DWITH_FAST_MUTEXES=1-DWITH_ZLIB=bundled-DENABLED_LOCAL_INFILE=1-DWITH_READLINE=1-DWITH_EMBEDDED_SERVER=1-DWITH_DEBUG=0

Echo $?

Make

Make install

Echo $?

5.2 interpretation of compilation parameters:

# / * the following are the configuration parameters comments * /

# / *-DCMAKE_INSTALL_PREFIX=/aliyun/server/mysql # specify the location where mysql is installed

# / *-DMYSQL_DATADIR=/data/mysql/data # location of data files

# / *-DSYSCONFDIR=/etc # location of the configuration file my.cnf

# / *-DWITH_MYISAM_STORAGE_ENGINE=1 # supports MyIASM engine

# / *-DWITH_INNOBASE_STORAGE_ENGINE=1 # supports InnoDB engine

# / *-DWITH_MEMORY_STORAGE_ENGINE=1 # supports Memory engine

-DWITH_FEDERATED_STORAGE_ENGINE=1

-DWITH_BLACKHOLE_STORAGE_ENGINE=1

-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1

# / *-DWITH_READLINE=1 # Shortcut key function (I haven't used it)

# / *-DMYSQL_UNIX_ADDR=/tmp/mysql.sock# connection database socket path

# / *-DENABLED_LOCAL_INFILE=on # allows data to be imported locally

# / *-DEXTRA_CHARSETS=all # install all character sets

# / *-DDEFAULT_CHARSET=utf8 # default character

# / *-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii # set other additional character sets

# / *-DDEFAULT_COLLATION=utf8_general_ci # specifies the default proofreading rules for the server, default latin1_general_ci

# / *-DWITH_ZLIB=bundled

# / *-DWITH_EMBEDDED_SERVER=1

Additional compilation parameters:

# / *-DWITH_xxx_STORAGE_ENGINE # specifies the storage engine statically compiled to mysql

MyISAM,MERGE,MEMORY and CSV engines are compiled to the server by default and do not need to be specified [1. 0]. On behalf of compilation]

# / *-DWITHOUT_xxx_STORAGE_ENGINE # specifies a storage engine that is not compiled

# / *-DWITH_EXTRA_CHARSETS:STRING=utf8,gbk

5.3 compilation and installation Times error 1. 63% of errors are reported during compilation and installation:

Error reporting problem in make: 63% error report when compiling and installing

Make [2]: * * [storage/perfschema/unittest/pfs_connect_attr-t] Error 1

Make [1]: * * [storage/perfschema/unittest/CMakeFiles/pfs_connect_attr-t.dir/all] Error 2

Solution:

Cmake removes the-DWITH_PARTITION_STORAGE_ENGINE=1 parameter and recompiles

-DWITH_PARTITION_STORAGE_ENGINE=1 # installation supports database partitioning

two。 Compiler error report

-bash:-DMYSQL_UNIX_ADDR=/tmp/mysql.sock: No suchfile or directory

Remove this parameter, delete the extracted directory, reconnect the package, and recompile

3. The MySQL service that is compiled and installed cannot be started due to insufficient memory

The leased server is Ariyun's beggar version (minimum configuration), single core, 512 megabytes of memory, 1 megabyte bandwidth.

The solution is to modify or decrease the value of innodb_buffer_pool_size in my.cnf

As a result, the netizen set 1G at that time, which must have been blown. Later, it was set to 100m, but it was still not enough. Finally, it was set to 80m. Finally, the tearful start was successful.

If the command line prompts The server quit without updating PID file, please check whether the group and user of the data directory are mysql, and check the log carefully to see if the configuration is too high to start.

5.4 continue to compile and install after shooting the error

Make

Make install

Echo $?

Make a soft connection:

Ln-s mysql-5.6.16 mysql

Cd / aliyun/server/

Ll

Lrwxrwxrwx. 1 root root 12 May 16 22:41mysql-> mysql-5.6.16

Chown-R mysql.mysql / aliyun/server/mysql-5.6.16/

Cd / aliyun/server/mysql-5.6.16/support-files/

Cp my-default.cnf/etc/my.cnf

Cp mysql.server / etc/init.d/mysqld

Chmod + x / etc/init.d/mysqld (otherwise initializing MySQL below will report an error permission is not allowed)

[root@mysql-5 scripts] # / aliyun/server/mysql/scripts/mysql_install_db--user=mysql-- basedir=/aliyun/server/mysql-- datadir=/data/mysql/data

-bash:/aliyun/server/mysql/scripts/mysql_install_db:Permission denied

[root@mysql-5 scripts] # ll mysql_install_db

-rw-r--r--. 1 mysql mysql 33046 May 16 23:38mysql_install_db

[root@mysql-5 scripts] # chmod + x mysql_install_db

5.5 initialize MySQL to start MySQL

[root@mysql-5 scripts] # / aliyun/server/mysql/scripts/mysql_install_db--user=mysql-- basedir=/aliyun/server/mysql-- datadir=/data/mysql/data

/ etc/init.d/mysqld start

/ etc/init.d/mysqld stop

/ etc/init.d/mysqld status

Echo 'export PATH=/aliyun/server/mysql/bin:$PATH' > > / etc/profile

Source / etc/profile

An error occurred when starting MySQL after initialization: the lsof-iRank 3306 service did not start.

[root@mysql-5 ~] # / etc/init.d/mysqld start

Starting MySQL... ERROR! The server quit withoutupdating PID file (/ var/lib/mysql/mysql-5.6s1.pid).

The reason is that when cp my-default.cnf / etc/my.cnf, there is no cp success.

After re-cp, re-initialize MySQL and start MySQL again. Successful

5.6 preliminary simple optimization of the database:

Set the password to the database:

Mysqladmin-uroot password 123456

Mysql > show databases

Mysql > select user,host from mysql.user

Mysql > drop user 'root'@'::1'

Mysql > drop user'@ 'localhost'

Mysql > drop user'@ 'mysql-5.6m1'

Mysql > drop user 'root'@'mysql-5.6m1'

Mysql > select user,host from mysql.user

+-+ +

| | user | host |

+-+ +

| | root | 127.0.0.1 | |

| | root | localhost |

+-+ +

2 rows in set (0.00 sec)

Mysql > drop database test

Query OK, 0 rows affected (0.03 sec)

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

+-+

3 rows in set (0.00 sec)

Change the password:

Mysqladmin-uroot-pendant 123456' password'111111'

Edit mysql5.6.16 profile

[root@mysql-5 tmp] # cat / etc/my.cnf

[client]

Socket = / tmp/mysqld.sock

Port = 3306

[mysqld]

Basedir = / aliyun/server/mysql

Datadir = / data/mysql/data

Socket = / tmp/mysqld.sock

Port = 3306

Server_id = 100

User = mysql

Join_buffer_size = 128m

Sort_buffer_size = 2m

Read_rnd_buffer_size = 2m

Default_storage_engine = InnoDB

Join_buffer_size = 128m

Max_allowed_packet= 1M

Net_buffer_length= 8K

Skip-external-locking

Skip-host-cache

Skip-name-resolve

# InnoDB

Innodb_buffer_pool_size = 128m

Innodb_log_file_size = 48m

Innodb_file_per_table = 1

Innodb_flush_method = O_DIRECT

# MyISAM

Key_buffer_size = 48m

Character-set-server=utf8

Collation-server=utf8_general_ci

# LOG

Log_error = / data/mysql/data/mysql-error.log

Long_query_time = 1

Slow-query-log

Slow_query_log_file = / data/mysql/data/mysql-slow.log

# Others

Explicit_defaults_for_timestamp=true

Max_connections = 500,

Open_files_limit = 65535

Sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

There must be something wrong with the setting of the parameters in the configuration file when you start the error report. Check that the configuration file turns out to be the wrong path, log_error = / data/mysql/data/mysql-error.log

[root@mysql-5 scripts] # / etc/init.d/mysqldstart

Starting MySQL. ERROR! The server quitwithout updating PID file (/ data/mysql/data/mysql-5.6s1.pid).

Mysql5.6 initializes the display:

Detailed description of parameters related to the configuration file:

[root@mysql-5 scripts] # / aliyun/server/mysql/scripts/mysql_install_db--user=mysql-- basedir=/aliyun/server/mysql-- datadir=/data/mysql/data

WARNING: The host 'mysql-5.6m1'could not be looked up with / aliyun/server/mysql/bin/resolveip.

This probably means that your libc libraries arenot 100% compatible

With this binary MySQL version. The MySQL daemon,mysqld, should work

Normally with the exception that host nameresolving will not work.

This means that you should use IP addresses insteadof hostnames

When specifying MySQL privileges!

Installing MySQL system tables...2016-05-1700 TIMESTAMP with implicit DEFAULTvalue is deprecated 13 TIMESTAMP with implicit DEFAULTvalue is deprecated. Please use-explicit_defaults_for_timestamp server option (see documentation for more details).

2016-05-17 00:13:04 9551 [Note] InnoDB: Usingatomics to ref count buffer pool pages

2016-05-17 00:13:04 9551 [Note] InnoDB: The InnoDBmemory heap is disabled

2016-05-17 00:13:04 9551 [Note] InnoDB: Mutexes andrw_locks use GCC atomic builtins

2016-05-17 00:13:04 9551 [Note] InnoDB: Compressedtables use zlib 1.2.3

2016-05-17 00:13:04 9551 [Note] InnoDB: Using CPUcrc32 instructions

2016-05-17 00:13:04 9551 [Note] InnoDB:Initializing buffer pool, size = 128.0m

2016-05-17 00:13:04 9551 [Note] InnoDB: Completedinitialization of buffer pool

2016-05-17 00:13:04 9551 [Note] InnoDB: The firstspecified data file. / ibdata1 did not exist: a new database to be created!

2016-05-17 00:13:04 9551 [Note] InnoDB: Settingfile. / ibdata1 size to 12 MB

2016-05-17 00:13:04 9551 [Note] InnoDB: Databasephysically writes the file full: wait...

2016-05-17 00:13:04 9551 [Note] InnoDB: Setting logfile. / ib_logfile101 size to 48 MB

2016-05-17 00:13:05 9551 [Note] InnoDB: Setting logfile. / ib_logfile1 size to 48 MB

2016-05-17 00:13:06 9551 [Note] InnoDB: Renaminglog file. / ib_logfile101 to. / ib_logfile0

2016-05-17 00:13:06 9551 [Warning] InnoDB: New logfiles created, LSN=45781

2016-05-17 00:13:06 9551 [Note] InnoDB: Doublewritebuffer not found: creating new

2016-05-17 00:13:06 9551 [Note] InnoDB: Doublewritebuffer created

2016-05-17 00:13:06 9551 [Note] InnoDB: 128rollbacksegment (s) are active.

2016-05-17 00:13:06 9551 [Warning] InnoDB: Creatingforeign key constraint system tables.

2016-05-17 00:13:06 9551 [Note] InnoDB: Foreign keyconstraint system tables created

2016-05-17 00:13:06 9551 [Note] InnoDB: Creating tablespaceand datafile system tables.

2016-05-17 00:13:06 9551 [Note] InnoDB: Tablespaceand datafile system tables created.

2016-05-17 00:13:06 9551 [Note] InnoDB: Waiting forpurge to start

2016-05-17 00:13:06 9551 [Note] InnoDB: 5.6.16 started; log sequence number 0

2016-05-17 00:13:06 9551 [Note] Binlog end

2016-05-17 00:13:06 9551 [Note] InnoDB: FTSoptimize thread exiting.

2016-05-17 00:13:06 9551 [Note] InnoDB: Startingshutdown...

2016-05-17 00:13:07 9551 [Note] InnoDB: Shutdowncompleted; log sequence number 1625977

OK

Filling help tables...2016-05-17 00:13:07 0 [Warning] TIMESTAMP with implicit DEFAULT value isdeprecated. Please use-explicit_defaults_for_timestamp server option (seedocumentation for more details).

2016-05-17 00:13:07 9574 [Note] InnoDB: Usingatomics to ref count buffer pool pages

2016-05-17 00:13:07 9574 [Note] InnoDB: The InnoDBmemory heap is disabled

2016-05-17 00:13:07 9574 [Note] InnoDB: Mutexes andrw_locks use GCC atomic builtins

2016-05-17 00:13:07 9574 [Note] InnoDB: Compressedtables use zlib 1.2.3

2016-05-17 00:13:07 9574 [Note] InnoDB: Using CPUcrc32 instructions

2016-05-17 00:13:07 9574 [Note] InnoDB:Initializing buffer pool, size = 128.0m

2016-05-17 00:13:07 9574 [Note] InnoDB: Completedinitialization of buffer pool

2016-05-17 00:13:07 9574 [Note] InnoDB: Highestsupported file format is Barracuda.

2016-05-17 00:13:07 9574 [Note] InnoDB: 128rollback segment (s) are active.

2016-05-17 00:13:07 9574 [Note] InnoDB: Waiting forpurge to start

2016-05-17 00:13:07 9574 [Note] InnoDB: 5.6.16 started; log sequence number 1625977

2016-05-17 00:13:07 9574 [Note] Binlog end

2016-05-17 00:13:07 9574 [Note] InnoDB: FTSoptimize thread exiting.

2016-05-17 00:13:07 9574 [Note] InnoDB: Startingshutdown...

2016-05-17 00:13:09 9574 [Note] InnoDB: Shutdowncompleted; log sequence number 1625987

OK

To start mysqld at boot time you have to copy

Support-files/mysql.server to the right place foryour system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQLroot USER!

To do so, start the server, then issue thefollowing commands:

/ aliyun/server/mysql/bin/mysqladmin-u rootpassword 'new-password'

/ aliyun/server/mysql/bin/mysqladmin-u root-h mysql-5.6m1 password'new-password'

Alternatively you can run:

/ aliyun/server/mysql/bin/mysql_secure_installation

Which will also give you the option of removing thetest

Databases and anonymous user created bydefault. This is

Strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:

Cd. ; / aliyun/server/mysql/bin/mysqld_safe &

You can test the MySQL daemon withmysql-test-run.pl

Cdmysql-test; perl mysql-test-run.pl

Please report any problems at http://bugs.mysql.com/

The latest information about MySQL is available onthe web at

Http://www.mysql.com

Support MySQL by buying support/licenses at http://shop.mysql.com

New default config file was created as/aliyun/server/mysql/my.cnf and

Will be used by default by the server when youstart it.

You may edit this file to change server settings

WARNING: Default config file/etc/my.cnf exists on the system

This file will be read by default by the MySQLserver

If you do not want to use this, either remove it,or use the

-- defaults-file argument to mysqld_safe whenstarting the server

Chown mysql.mysql-R / aliyun/server/mysql

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.