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

1. System environment CentOS 6.8_X64

2. Required software mirrors-alisql-master.zip and required dependency package centos-release-scl devtoolset-4-gcc-c++ devtoolset-4-gcc ncurses-devel openssl-devel bison git cmake

3. Install the required dependent software packages

Yum-y install centos-release-scl devtoolset-4-gcc-c++ devtoolset-4-gcc ncurses-devel openssl-devel bison git cmake

4. Download mirrors-alisql-master.zip

Https://github.com/alibaba/AliSQL

5. Decompress mirrors-alisql-master.zip

Unzip mirrors-alisql-master.zip

6. Create Mysql user groups and users, and database storage directory:

Mkdir-p / usr/local/mysql

Mkdir-p / usr/local/mysql/tmp

Mkdir-p / var/log/mysql

Groupadd mysql

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

Chown-R mysql.mysql / usr/local/mysql

Chown-R mysql.mysql / var/log/mysql

7. Compile and install

Cd alisql

Cmake. \

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql\

-DMYSQL_DATADIR=/usr/local/mysql/data\

-DCMAKE_BUILD_TYPE= "Release"\

-DWITH_EMBEDDED_SERVER=0\

-DWITH_EXTRA_CHARSETS=all\

-DWITH_MYISAM_STORAGE_ENGINE=1\

-DWITH_INNOBASE_STORAGE_ENGINE=1\

-DWITH_PARTITION_STORAGE_ENGINE=1\

-DWITH_CSV_STORAGE_ENGINE=1\

-DWITH_ARCHIVE_STORAGE_ENGINE=1\

-DWITH_BLACKHOLE_STORAGE_ENGINE=1\

-DWITH_FEDERATED_STORAGE_ENGINE=1\

-DWITH_PERFSCHEMA_STORAGE_ENGINE=1\

-DWITH_TOKUDB_STORAGE_ENGINE=0\

-DENABLE_DTRACE=0\

-DENABLED_LOCAL_INFILE=1\

-DDEFAULT_CHARSET=utf8mb4\

-DDEFAULT_COLLATION=utf8mb4_general_ci\

-DEXTRA_CHARSETS=all\

-DMYSQL_USER=mysql\

-DWITH_MEMORY_STORAGE_ENGINE=1\

-DENABLE_DOWNLOADS=1\

-DWITH_DEBUG=OFF\

-DENABLED_PROFILING=ON\

-DMYSQL_MAINTAINER_MODE=OFF

Scl enable devtoolset-4 bash # before configuration, you need to set the environment variable so that you can use gcc in the devtoolset-4 suite

Make-J4 & & make install #-j4 means 4 concurrent compilation processes are opened to speed up compilation, which is adjusted according to the number of CPU cores of the machine. Generally, the number of CPU cores is + 1.

8. Edit the configuration file

[client]

Port = 3306

Socket = / usr/local/mysql/mysql.sock

Default-character-set = utf8mb4

[mysql]

Prompt= "AliSQL [\ d] >"

# no-auto-rehash

[mysqld]

Port = 3306

User = mysql

Skip-name-resolve

Socket = / usr/local/mysql/mysql.sock

Basedir = / usr/local/mysql

Datadir = / usr/local/mysql/data

Pid-file = / usr/local/mysql/mysql.pid

Tmpdir = / usr/local/mysql/tmp

Slave-load-tmpdir = / usr/local/mysql/tmp

Log_error = / var/log/mysql/mysqld.log

# secure-file-priv = / usr/local/mysql/uploads

# slow query

Slow_query_log = 1

Long_query_time = 2

Log-queries-not-using-indexes

Slow_query_log_file = / var/log/mysql/mysql_slow.log

Log-slow-admin-statements = 1

Log-slow-slave-statements = 1

Log-bin-trust-function-creators = 1

# init-connect = 'SET NAMES utf8'

# character-set-server = utf8

Init-connect = 'SET NAMES utf8mb4'

Character-set-server = utf8mb4

Symbolic-links = 0

# lower_case_table_names = 1

Performance_schema = 0

# bind-address = 0.0.0.0

# enable query caching

Explicit_defaults_for_timestamp=true

# consume system IO memory, etc.

# log-output = FILE

# general_log = off

# general_log_file = / var/log/mysql/general.log

# # ^ ^ start with synchronization-related settings ^ # #

Server-id = 10

Binlog_cache_size = 4m

Max_binlog_size = 1G

Max-binlog-cache-size = 2G

Max-relay-log-size = 1G

Log_bin = / var/log/mysql/mysql-master-bin

Binlog_format = mixed

Log_bin_index = / var/log/mysql/bin_log.index

Expire_logs_days = 90

# skip-locking

# prohibit public network

# skip-networking

# prohibit domain name resolution

Skip-name-resolve

# skip-grant-tables

# No locking

Skip-external-locking

Skip_show_database

Skip_symbolic_links

Back_log = 300

Max_connections = 1000

Max_connect_errors = 6000

Open_files_limit = 65535

# Table caches

Table_open_cache = 128,

Binlog_cache_size = 1m

# maximum table quantity

Max_heap_table_size = 256m

Tmp_table_size = 16m

# the rules for setting physical memory are as follows: 1G=8; 2Gateway 16; 3Gmail 32; 3G > = 64

Thread_cache_size = 16

Open-files-limit=8192

# sort buffering

Sort_buffer_size=256M

# start caching

Query_cache_type = 1

# query cache rarely has the same query, so it is best not to use query cache

Query_cache_size = 128m

Query_cache_limit = 2m

Flush_time = 0

# continuous buffering

Join_buffer_size = 16m

Group_concat_max_len=2048000

Table_definition_cache = 1400

Ft_min_word_len = 4

# pre-read buffer

Read_buffer_size = 2m

# buffers

Read_rnd_buffer_size = 64m

Query_cache_min_res_unit = 1MB

Bulk_insert_buffer_size = 8m

# ideal setting for index buffering: Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%

Key_buffer_size=64M

# default-storage-engine = MyISAM

Default_storage_engine = InnoDB

Innodb_file_per_table = 1

Innodb_open_files = 500,

# parameters are configured between 70% of physical memory and 80% of physical memory

Innodb_buffer_pool_size = 64m

# InnoDB uses background threads to process the number of requests written on the data page that are written on the I _ sign O (input)

Innodb_write_io_threads = 4

# InnoDB uses background threads to process the number of read I _ peg O (output) requests on a data page

Innodb_read_io_threads = 4

Innodb_thread_concurrency = 0

Innodb_purge_threads = 1

Innodb_flush_log_at_trx_commit = 2

# Log buffer pool, which can be controlled in 8M-16M

Innodb_log_buffer_size = 2m

# size of InnoDB log file

Innodb_log_file_size = 256m

Innodb_log_files_in_group = 3

# controls the percentage of Dirty Page in Buffer Pool

Innodb_max_dirty_pages_pct = 90

Innodb_lock_wait_timeout = 120

# when the buffer is flushed to disk, the number of dirty pages can be flushed. After ssd disk or raid, you can set this value to a higher value.

Innodb_io_capacity = 200

Innodb_adaptive_flushing = OFF

Innodb_autoextend_increment = 64

Innodb_buffer_pool_instances = 8

Innodb_concurrency_tickets = 5000

Innodb_old_blocks_time = 1000

Innodb_stats_on_metadata = 0

Myisam_sort_buffer_size = 16m

Myisam_max_sort_file_size = 10G

Myisam_repair_threads = 1

# methods of importing large sql files

Interactive_timeout = 28800

Wait_timeout = 28800

Connect_timeout = 20

# single data package

Max_allowed_packet = 500m

# thread_concurrency = 8

[mysqlhotcopy]

Interactive-timeout

[mysqldump]

Quick

Max_allowed_packet = 500m

[myisamchk]

Key_buffer_size = 8m

Sort_buffer_size = 8m

Read_buffer = 4m

Write_buffer = 4m

[mysqld_safe]

Log_error = / var/log/mysql/mysqld.log

Pid-file = / usr/local/mysql/mysql.pid

Chown mysql.mysql / etc/my.cnf

Chmod 600 / etc/my.cnf

9. Initialize the database: (note that a random password for root will be generated during initialization, which will be used later in login)

Chmod 777 scripts/mysql_install_db

Scripts/mysql_install_db-basedir=/usr/local/mysql-datadir=/usr/local/mysql/data-user=mysql-defaults-file=/etc/my.cnf

10. Set environment variables

Vim / etc/profile

Export PATH=$PATH:/usr/local/mysql/bin:/usr/local/mysql/lib

11. Copy the mysql service startup configuration file

Cp / usr/local/mysql/support-files/mysql.server / etc/init.d/mysqld

Chmod axix / etc/init.d/mysqld

12. Start the database process

Service mysqld start

13. Change the root password and set remote login

Mysql-u root

AliSQL [mysql] > use mysql

AliSQL [mysql] > select host, user from user

AliSQL [mysql] > update user set password=PASSWORD ('rootoo') where user='root'

AliSQL [mysql] > update user set host ='% 'where user =' root'; # authorized remote login user name is root. If an error is reported, ERROR 1062 (23000): Duplicate entry'%-root' for key 'PRIMARY', will ignore it.

AliSQL [mysql] > flush privileges

AliSQL [mysql] > grant all on. To "root" @ "172.30.41.72" identified by "123456"; # authorize 172.30.41.72 to access the database as a root user with a root password of 123456

AliSQL [mysql] > grant all on. To "root" @ "%" identified by "123456"; # authorize all ip to remotely log in to the database as a root user with a root password of 123456

AliSQL [mysql] > grant all on. To "root" @ "localhost" identified by "123456"; # authorize localhost to remotely log in as root user to access the database with a root password of 123456

AliSQL [mysql] > select user from user; # View all users

14. Restart mysql

Service mysqld restart

Chkconfig mysqld on

15. Download phpMyAdmin

Wget https://files.phpmyadmin.net/phpMyAdmin/4.7.0/phpMyAdmin-4.7.0-all-languages.zip

16. Decompress phpMyAdmin-4.7.0-all-languages.zip

Unzip phpMyAdmin-4.7.0-all-languages.zip

17. Open the config.default.php file under libraries, find the following items in turn, and follow the instructions to configure:

a. Visit the web site

$cfg ['pmaabsoluteuri'] =' 192.168.1.5'; fill in the URL of phpmyadmin here

B.mysql host information

$cfg ['servers'] [$I] [' host'] = 'localhost'; / / mysql hostname or ip address

Enter the ip address of the server where the localhost or mysql resides. If the mysql and the phpmyadmin are on the same server, press the default localhost

$cfg ['servers'] [$I] [' port'] =''; / / mysql port-leave blank for default port

Mysql port. If default is 3306, leave it blank.

C.mysql username and password

$cfg ['servers'] [$I] [' user'] = 'root'; / / the mysql user name used by mysql user to access phpmyadmin

Fg ['servers'] [$I] [' password'] = 'rootoo'; / / mysql password (only needed corresponds to the password of the above mysql user name)

d. Authentication method

$cfg ['servers'] [$I] [' auth_type'] = 'http'

There are four modes to choose from here, cookie,http,http,config

In config mode, you can enter the URL of phpmyadmin directly without entering a user name and password, which is not safe and is not recommended.

When this item is set to cookie,http or http, login to phpmyadmin requires data username and password for authentication, as follows:

Php installation mode is nginx/apache, you can use http and cookie

Php installation mode is cgi, you can use cookie

e. Setting of phrase password (blowfish_secret)

$cfg ['blowfish_secret'] =''

If the authentication method is set to cookie, you need to set the phrase password, but you cannot leave it blank, otherwise you will get an error when logging in to phpmyadmin.

f. Configuration

1. Open the / libraries/config.default.php file (the old version is the config.inc.php file in the root directory), edit it with WordPad (do not use notepad, this is utf8 coding), and configure it according to the instructions.

2. Find $cfg ['pmaabsoluteuri'] =''; / / change it to the URL of the phpmyadmin that you will upload to the space

For example, $cfg ['pmaabsoluteuri'] =' http: / / website domain name / phpmyadmin/'

3. Find $cfg ['servers'] [$I] [' host'] = 'localhost'; / / usually use default, there are exceptions, and you don't have to modify it

4. Find $cfg ['servers'] [$I] [' auth_type'] = 'config'; / / debug config; in your own machine if you use cookie in the space on the network.

18. Http://192.168.1.5/phpMyAdmin/ enters the user name, root password, rootoo, and the access is normal.

Common error: # 2002-Permission denied-the server is not responding (or the socket of the local server is not set correctly).

Reason for the error: if no hostname is specified or a special hostname localhost is specified, the Unix socket is used.

The Unix socket defaults to / tmp/mysql.sock. If such a socket file is not configured or the configuration is incorrect, the connection will naturally fail.

Solution: $cfg ['servers'] [$I] [' host'] = 'localhost'; changed to $cfg [' servers'] [$I] ['host'] =' 127.0.0.1'

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.