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

In general, there are 1-4 instances, with 1-2 instances in the majority.

I. installation environment

2. Add multiple instances (install mysql)

Third, the addition of configuration files

IV. Add the startup script

V. the authority and ownership of the document

Configure the global path

Initialize the database

VIII. Mysql starts, closes, and logs in

I. installation environment

CentOS Linux release 7.2.1511

Installation directory: / application/mysql

Installation version: mysql-5.7.9

2. Add the instance (install mysql)

See blog post mysql install https://blog.51cto.com/13006632/2457769

Mkdir / data/ {3306 < 3307} / data-p

Third, the addition of configuration file my.cnf

[client] port = 3306 # instance port The other is 3307socket = / data/3306/ mysql.sock [MySQL] no-auto- rehash [mysqld] user = mysqlport = 3306socket = / data/3306/mysql.sockbasedir = / application/mysqldatadir = / data/3306/dataopen_files_limit = 1024back_log = 600max_connections = 800max_connect_errors = 614external-locking = FALSEmax_allowed_packet = 8Msort_buffer_size = 1Mjoin_buffer_size = 1Mthread_cache_size = 100query_cache_size = 2Mquery_cache_limit = 1M#default_table_type = InnoDBthread_stack = 192k#transaction_isolation = READ-COMMITTEDtmp_table_size = 2Mmax_heap_table_size = 2Mlong_query_time = 1#log_long_format#log-error = / data/3306/error.log#log-slow-queries = / data/3306/slow.logpid-file = / data/3306/mysql.pidlog-bin = / data/3306/mysql-binrelay-log = / data/3306/relay-binrelay-log-info-file = / data/3306/relay -log.infobinlog_cache_size = 1Mmax_binlog_cache_size = 1Mmax_binlog_size = 2m expire_logs_days = 7 key_buffer_size = 16m read_buffer_size = 1m read_rnd_buffer_size = 1m bulk_insert_buffer_size = 1m bulk_insert_buffer_size = 1m myisam _ sort_buffer_size = 1M#myisam_max_extra_sort_file_size = 10G # uyisam_repair_threads = 1 # yisam_recoverlower_case_table_names = 1skip-name-resolveslave-skip-errors = 1032 Db = mysqlserver-id = 8 innodb_buffer_pool_size = 32Minnodb_data_file_path = ibdata1:128M:autoextend innodb_thread_concurrency = 8 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 2Minnodb _ log_file_size = 4Minnodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 innodb_file_per_table = 0 [mysqldump] quickmax_allowed_packet = 2m [mysqld _ safe] log -error = / data/3306/mysql_3306.err pid-file = / data/3306/mysqld.pid

IV. Addition of mysql script

#! / bin/sh#initport=3306mysql_user= "root" mysql_pwd= "123456" CmdPath= "/ application/mysql/bin" mysql_sock= "/ data/$ {port} / mysql.sock" # startup functionfunction_start_mysql () {if [!-e "$mysql_sock"] Then printf "Starting MySQL...\ n" / bin/sh ${CmdPath} / mysqld_safe-- defaults-file=/data/$ {port} / my.cnf 2 > & 1 > / dev/null & else printf "MySQL is running...\ n" exit fi} # stop functionfunction_stop_mysql () {if [!-e "$mysql_sock"] Then printf "MySQL is stopped...\ n" exit else printf "Stoping MySQL...\ n" ${CmdPath} / mysqladmin-u ${mysql_user}-p$ {mysql_pwd}-S / data/$ {port} / mysql.sock shutdown fi} # restart functionfunction_restart_mysql () {printf "Restarting MySQL...\ N "function_stop_mysql sleep 2 function_start_mysql} case $1 instart) function_start_mysql Stop) function_stop_mysql;;restart) function_restart_mysql;;*) printf "Usage: / data/$ {port} / mysql {start | stop | restart}\ n" esac

Special note: from the education of old boys

Startup principle

/ bin/sh / application/mysql/bin/mysqld_safe-- defaults-file=/data/3306/my.cnf

Stopping principle

/ application/mysq/bin/mysqladmin-uroot-poldboy-S / data/3306/mysql.sock shutdown

Modify my.cnf to 3306 and 3307 and mysql script, put it under / data/3306 and / data/3307

[root@mysql data] # tree. ├── 3306 │ ├── data │ ├── my.cnf │ └── mysql └── 3307 ├── data ├── my.cnf └── mysql4 directories, 4 files

V. the authority and ownership of the document

Chown-R mysql.mysql / datafind / data-type f-name "mysql" | xargs chmod 700find / data-type f-name "mysql" | xargs ls-l

VI. Global path configuration

Echo 'export PATH=/application/mysq/bin:$PATH' > > / etc/profiletail-1 / etc/profilesource / etc/profile

Initialize the database

Cd / application/mysql/./bin/mysqld-defaults-file=/data/3306/my.cnf-initialize-basedir=/application/mysql/-datadir=/data/3306/data/-user=mysql./bin/mysqld-defaults-file=/data/3307/my.cnf-initialize-basedir=/application/mysql/-datadir=/data/3307/data/-user=mysql

VIII. Mysql starts, closes, and logs in

/ data/3307/mysql start/data/3307/mysql stop#mysql login mysql-S / data/3307/mysql.sock# modify password mysqladmin-uroot-paired password 'password' 123456'-S / data/3307/mysql.sock# remote login multi-instance mysql-uuser-paired password'-h292.168.1.23-P 3307

IX. Error reporting

Error 1:

It won't start up. The log shows: 2019-12-12T17:51:06.643562Z 0 [ERROR] InnoDB: The Auto-extending innodb_system data file'. / ibdata1' is of a different size 768 pages (rounded down to MB) than specified in the .cnf file: initial 8192 pages Max 0 (relevant if non-zero) pagesystems 2019-12-12T17:51:06.643597Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error2019-12-12T17:51:06.945987Z 0 [ERROR] Plugin 'InnoDB' init function returned error.2019-12-12T17:51:06.946185Z 0 [ERROR] Plugin' InnoDB' registration as a STORAGE ENGINE failed.2019-12-12T17:51:06.946203Z 0 [ERROR] Failed to initialize plugins.2019-12-12T17:51:06.946214Z 0 [ERROR] Aborting

Solution:

Initialization without configuration file reinitialization

. / bin/mysqld-- initialize-- defaults-file=/data/3307/my.cnf-- basedir=/application/mysql/-- datadir=/data/3307/data/-- there is a problem with the order of parameters: error 2

Error 2:

2019-12-12T18:24:10.667460Z 0 [ERROR] unknown variable 'defaults-file=/data/3307/my.cnf'2019-12-12T18:24:10.667497Z 0 [ERROR] Aborting

For the reason, see blog: https://www.cnblogs.com/jerry-rock/p/7380922.html

The solution is modified as follows:

. / bin/mysqld-defaults-file=/data/3307/my.cnf-initialize-basedir=/application/mysql/-datadir=/data/3307/data/-user=mysql

Error 3:

2019-12-12T18:22:50.814935Z 0 [ERROR]-- initialize specified but the data directory has files in it. Aborting.2019-12-12T18:22:50.814979Z 0 [ERROR] Aborting

Reason: the / data/3307/data/ directory has old initialization files that need to be deleted and emptied.

Solution:

Rm-fr / data/3307/data/*

Error 4:

2019-12-12T18:29:32.841614Z 0 [ERROR] unknown variable 'table_cache=64' # changed to table_open_cache 2019-12-12T18:29:32.841644Z 0 [ERROR] unknown variable' thread_concurrency=2' # need to delete 2019-12-12T19:04:17.320405Z 0 [ERROR] unknown variable 'innodb_additional_mem_pool_size=4M' # need to delete 2019 -12-12T19:14:21.886638Z 0 [ERROR] unknown variable 'innodb_file_io_threads=4' # need to delete 2019-12-12T18:29:32.841644Z 0 [ERROR] Aborting

For the reason, see blog: https://www.cnblogs.com/putihk/p/7967829.html

Mysql version 5.7. the parameter names in the my.cnf configuration file I used were changed or deleted.

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.