成人av一级,久久99精品久久久久久噜噜,国产999精品久久久久

因使用源碼安裝的MySQL5.7.28多實(shí)例，在導(dǎo)入數(shù)據(jù)庫(kù)時(shí)會(huì)出現(xiàn)問(wèn)題，所以重新研究使用mysql_multi的方法來(lái)管理多實(shí)例，經(jīng)過(guò)測(cè)試環(huán)境驗(yàn)證之后，在各方面使用上特別在備份還原上，沒(méi)有報(bào)MySQL5.7.28多實(shí)例的問(wèn)題，踩了不少坑，這里我將我的部署過(guò)程分享下，如果在哪里出問(wèn)題的，還請(qǐng)多多指正與指導(dǎo)，謝謝！！

參考文章：Centos7.5安裝mysql5.7.24二進(jìn)制包方式部署

本從就直接從2.7章節(jié)開(kāi)始安裝mysql多實(shí)例，具體部署過(guò)程如下：

2.7 安裝mysql多實(shí)例

2.7.1. 創(chuàng)建軟件安裝目錄(部署路徑請(qǐng)根據(jù)實(shí)際修改)

				?

									[root@~]# mkdir -pv /data/mysql/{3306,3307}

									[root@~]# mkdir -v /data/mysql/3306/{logs,data,binlog}

									[root@~]# mkdir -v /data/mysql/3307/{logs,data,binlog}

2.7.2. MySQL安裝包下載

				?

									[root@~]# cd /opt

									[root@~]# wget -c https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz

									[root@~]# tar zxvf mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz

									[root@~]# mv mysql-5.7.28-linux-glibc2.12-x86_64 /usr/local/mysql

									[root@~]# chown -R mysql:mysql /usr/local/mysql

									[root@~]# chown -R mysql:mysql /data

2.7.3. MySQL參數(shù)配置

? 配置my.cnf參數(shù)文件

（1）server_id=3306與server_id=3307數(shù)值請(qǐng)根據(jù)實(shí)際配置，注意配置的id值與局域網(wǎng)內(nèi)其他各實(shí)例所配置的數(shù)值不可以沖突；

（2）max_connections=1000配置MySQL數(shù)據(jù)庫(kù)的最大連接數(shù)，根據(jù)實(shí)際需要配置，其他參數(shù)的優(yōu)化根據(jù)實(shí)際需要修改或添

加；

（3）配置文件全部?jī)?nèi)容如下

				?

									[root@~]# vim /etc/my.cnf

									[mysqld_multi]

									mysqld = /usr/local/mysql/bin/mysqld

									mysqladmin = /usr/local/mysql/bin/mysqladmin

									log = /data/mysql/mysqld_multi.log 

									#user=root

									#pass=

									[mysql]

									prompt="\u@jsshapp \R:\m:\s [\d]> "

									no-auto-rehash

									[mysqld3306]

									user = mysql

									port = 3306

									symbolic-links = 0

									#basedir = /usr/

									datadir = /data/mysql/3306/data

									socket = /data/mysql/3306/mysql3306.sock

									pid-file = /data/mysql/3306/mysqld3306.pid

									server_id = 3306

									character_set_server = utf8

									max_connections = 1000

									skip_name_resolve = 1

									open_files_limit = 65536

									thread_cache_size = 64

									table_open_cache = 4096

									table_definition_cache = 1024

									table_open_cache_instances = 64

									max_prepared_stmt_count = 1048576

									explicit_defaults_for_timestamp = true

									log_timestamps = system

									binlog_format = row

									log_bin = /data/mysql/3306/binlog/mysql-bin

									binlog_rows_query_log_events = on

									expire_logs_days = 7

									binlog_cache_size = 4M

									max_binlog_cache_size = 2G

									max_binlog_size = 1G

									sync_binlog = 1

									log_bin_trust_function_creators = 1

									slow_query_log = on

									slow_query_log_file = /data/mysql/3306/data/slow.log

									log-error = /data/mysql/3306/logs/error.log

									log_queries_not_using_indexes = on

									long_query_time = 1.000000

									gtid_mode = on

									enforce_gtid_consistency = on

									default_storage_engine = innodb

									default_tmp_storage_engine = innodb

									innodb_data_file_path = ibdata1:12M:autoextend:max:2000M

									innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:2000M

									innodb_buffer_pool_filename = ib_buffer_pool

									innodb_log_files_in_group = 3

									innodb_log_file_size = 512M

									innodb_online_alter_log_max_size = 1024M

									innodb_open_files = 4096

									innodb_page_size = 16k

									innodb_thread_concurrency = 0

									innodb_read_io_threads = 4

									innodb_write_io_threads = 4

									innodb_purge_threads = 4

									innodb_page_cleaners = 4

									innodb_print_all_deadlocks = on

									innodb_lock_wait_timeout = 20

									innodb_spin_wait_delay = 128

									innodb_autoinc_lock_mode = 2

									innodb_io_capacity = 200

									innodb_io_capacity_max = 2000

									#innodb_flush_neighbors = 

									innodb_log_buffer_size = 8M

									innodb_flush_log_at_timeout = 1

									innodb_flush_log_at_trx_commit = 2

									innodb_buffer_pool_size = 1024M

									innodb_buffer_pool_instances = 4

									autocommit = 1

									innodb_buffer_pool_dump_pct = 25

									innodb_buffer_pool_dump_at_shutdown = ON

									innodb_buffer_pool_load_at_startup = ON

									[mysqld3307]

									user = mysql

									port = 3307

									symbolic-links = 0

									lower_case_table_names = 1

									#basedir = /usr/

									datadir = /data/mysql/3307/data

									socket = /data/mysql/3307/mysql3307.sock

									pid-file = /data/mysql/3307/mysqld3307.pid

									server_id = 3307

									character_set_server = utf8

									max_connections = 1000

									skip_name_resolve = 1

									open_files_limit = 65536

									thread_cache_size = 64

									table_open_cache = 4096

									table_definition_cache = 1024

									table_open_cache_instances = 64

									max_prepared_stmt_count = 1048576

									explicit_defaults_for_timestamp = true

									log_timestamps = system

									binlog_format = row

									log_bin = /data/mysql/3307/binlog/mysql-bin

									binlog_rows_query_log_events = on

									expire_logs_days = 7

									binlog_cache_size = 4M

									max_binlog_cache_size = 2G

									max_binlog_size = 1G

									sync_binlog = 1

									slow_query_log = on

									slow_query_log_file = /data/mysql/3307/data/slow.log

									log-error = /data/mysql/3307/logs/error.log

									log_queries_not_using_indexes = on

									long_query_time = 1.000000

									gtid_mode = on

									enforce_gtid_consistency = on

									default_storage_engine = innodb

									default_tmp_storage_engine = innodb

									innodb_data_file_path = ibdata1:12M:autoextend:max:2000M

									innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:2000M

									innodb_buffer_pool_filename = ib_buffer_pool

									innodb_log_files_in_group = 3

									innodb_log_file_size = 512M

									innodb_online_alter_log_max_size = 1024M

									innodb_open_files = 4096

									innodb_page_size = 16k

									innodb_thread_concurrency = 0

									innodb_read_io_threads = 4

									innodb_write_io_threads = 4

									innodb_purge_threads = 4

									innodb_page_cleaners = 4

									innodb_print_all_deadlocks = on

									innodb_lock_wait_timeout = 20

									innodb_spin_wait_delay = 128

									innodb_autoinc_lock_mode = 2

									innodb_io_capacity = 200

									innodb_io_capacity_max = 2000

									#innodb_flush_neighbors = 

									innodb_log_buffer_size = 8M

									innodb_flush_log_at_timeout = 1

									innodb_flush_log_at_trx_commit = 2

									innodb_buffer_pool_size = 1024M

									innodb_buffer_pool_instances = 4

									autocommit = 1

									innodb_buffer_pool_dump_pct = 25

									innodb_buffer_pool_dump_at_shutdown = ON

									innodb_buffer_pool_load_at_startup = ON

									[mysqldump]

									quick

									max_allowed_packet = 32M

2.7.4. 配置MySQL環(huán)境變量

				?

									[root@~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' >> /etc/profile

									[root@~]# tail -1 /etc/profile

									[root@~]# source /etc/profile

2.7.5. testone實(shí)例初始化

? 操作實(shí)例初始化

				?

									[root@~]# mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql/ --user=mysql --datadir=/data/mysql/3306/data/ > /tmp/3306.log 2>&1

									[root@~]# tail -100f /tmp/3306.log

									---使用tail命令查看初始化日志，有出現(xiàn)如下內(nèi)容，即表示初始化完成（其中#5+t+xYW+<t?即為root用戶的臨時(shí)密碼）

									A temporary password is generated for root@localhost: #5+t+xYW+<t?

? 生成ssl文件

				?

									[root@~]# mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3306/data

? 啟動(dòng)testone實(shí)例

				?

									[root@~]# mysqld_multi start 3306

									[root@~]# tail -100f /data/mysql/3306/logs/error.log 

									----使用tail命令查看啟動(dòng)日志，有出現(xiàn)即表示啟動(dòng)成功

									Version: '5.7.28-log' socket: '/data/mysql/3306/mysql3306.sock' port: 3306 MySQL Community Server (GPL)

? 修改root密碼

				?

									[root@~]# less /tmp/3306.log | grep 'A temporary password'

									[root@~]# mysql -uroot -p -S /data/mysql/3306/mysql3306.sock

									Enter password:

									mysql> alter user 'root'@'localhost' identified by '統(tǒng)一密碼';

									mysql> flush privileges;

									mysql> exit;

? 驗(yàn)證 testone root用戶統(tǒng)一密碼（界面正常輸出information_schema內(nèi)容表示正常）

				?

									[root@~]# mysql -uroot -p -S /data/mysql/3306/mysql3306.sock -e "show databases;" | grep information_schema

									Enter password:

? 修改my.cnf配置文件，將修改的統(tǒng)一密碼，添加到配置文件中

				?

									[root@~]# sed -i "s@^#user=root@user=root@g" /etc/my.cnf

									[root@~]# sed -i "s@^#pass=@pass=統(tǒng)一密碼@g" /etc/my.cnf

									[root@~]# cat /etc/my.cnf | grep pass= ---使用cat命令查看配置文件pass字段輸出的結(jié)果是否一致

? 停止testone實(shí)例

				?

									[root@~]# mysqld_multi stop 3306

									[root@~]# netstat -tnlp|grep 3306 ---輸入結(jié)果為空表示服務(wù)停止正常

2.7.6. testtwo實(shí)例初始化

? 操作實(shí)例初始化

				?

									[root@~]# mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql/ --user=mysql --datadir=/data/mysql/3307/data/ > /tmp/3307.log 2>&1

									[root@~]# tail -100f /tmp/3307.log

									---使用tail命令查看初始化日志，有出現(xiàn)如下內(nèi)容，即表示初始化完成（其中-pn>t;Ye)Ay6=I即為root用戶的臨時(shí)密碼）

									A temporary password is generated for root@localhost: -pn>t;Ye)Ay6=I

									? 生成ssl文件

									[root@~]# mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3307/data

? 啟動(dòng)testwo實(shí)例

				?

									[root@~]# mysqld_multi start 3307

----使用tail命令查看啟動(dòng)日志，有出現(xiàn)即表示啟動(dòng)成功

				?

									[root@~]# tail -100f /data/mysql/3307/logs/error.log

									Version: '5.7.28-log' socket: '/data/mysql/3307/mysql3307.sock' port: 3307 MySQL Community Server (GPL)

? 修改root密碼

				?

									[root@~]# less /tmp/3307.log|grep 'A temporary password'

									[root@~]# mysql -uroot -p -S /data/mysql/3307/mysql3307.sock

									Enter password:

									mysql> alter user 'root'@'localhost' identified by '統(tǒng)一密碼';

									mysql> flush privileges;

									mysql> exit;

? 驗(yàn)證 testtwo root用戶統(tǒng)一密碼（界面正常輸出information_schema內(nèi)容表示正常）

				?

									[root@~]# mysql -uroot -p -S /data/mysql/3307/mysql3307.sock -e "show databases;" | grep information_schema

Enter password:

? 停止testtwo實(shí)例

				?

									[root@~]# mysqld_multi stop 3307

									[root@~]# netstat -tnlp|grep 3307 ---輸入結(jié)果為空表示服務(wù)停止正常

2.7.7. mysqld_multi多實(shí)例管理命令

? 啟動(dòng)全部實(shí)例

[root@~]# mysqld_multi start

? 停止單個(gè)實(shí)例

				?

									[root@~]# mysqld_multi stop 3306

									[root@~]# mysqld_multi stop 3307

? 啟動(dòng)單個(gè)實(shí)例

				?

									[root@~]# mysqld_multi start 3306

									[root@~]# mysqld_multi start 3307

? 查看全部實(shí)例的狀態(tài)(is running)

				?

									[root@~]# mysqld_multi report

									Reporting MySQL servers

									MySQL server from group: mysqld3306 is running

									MySQL server from group: mysqld3307 is running

? 查看單個(gè)實(shí)例狀態(tài)

				?

									[root@~]# mysqld_multi report 3306

									Reporting MySQL servers

									MySQL server from group: mysqld3306 is running

									[root@~]# mysqld_multi report 3307

									Reporting MySQL servers

									MySQL server from group: mysqld3307 is running

? 停止全部實(shí)例

[root@~]# mysqld_multi stop

2.7.8. 數(shù)據(jù)導(dǎo)入

（1）將待導(dǎo)入的數(shù)據(jù)庫(kù)腳本（test.sql、testtwo.sql）文件（名稱(chēng)根據(jù)實(shí)際情況操作）上傳至/data/路徑下

（2）使用命令確認(rèn)兩實(shí)例已經(jīng)處于運(yùn)行狀態(tài)

				?

									[root@~]# mysqld_multi report

									Reporting MySQL servers

									MySQL server from group: mysqld3306 is running

									MySQL server from group: mysqld3307 is running

（3）進(jìn)入testone的mysql數(shù)據(jù)庫(kù)創(chuàng)建testone數(shù)據(jù)庫(kù)實(shí)例、用戶名及密碼并導(dǎo)入數(shù)據(jù)

				?

									[root@~]# cd /data/

									[root@~]# mysql -uroot -p -S /data/mysql/3306/mysql3306.sock

									Enter password: 

									mysql>create database testone default character set utf8 collate utf8_bin;

									mysql> grant select,insert,update,delete,create,alter,execute on testone.* to 'testone'@'%' identified by '密碼';

									mysql> flush privileges;

									mysql> exit

									[root@~]# mysql -uroot -p -S /data/mysql/3306/mysql3306.sock testone < /data/test.sql

（3）進(jìn)入testtwo的mysql數(shù)據(jù)庫(kù)，創(chuàng)建testtwo服務(wù)數(shù)據(jù)庫(kù)實(shí)例、用戶名與密碼并導(dǎo)入數(shù)據(jù)

				?

									[root@~]# mysql -uroot -p -S /data/mysql/3307/mysql3307.sock

									Enter password:

									mysql> create database testtwo default character set utf8 collate utf8_bin;

									mysql> grant select,insert,update,delete,create,alter,execute on testtwo.* to 'testtwo'@'%' identified by '密碼';

									mysql> flush privileges;

									mysql> exit

									[root@~]# mysql -uroot -p -S /data/mysql/3307/mysql3307.sock testtwo < /data/testtwo.sql

2.7.9. 防火墻配置

? 根據(jù)實(shí)際要求，添加開(kāi)放端口

				?

									[root@~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent

									[root@~]# firewall-cmd --zone=public --add-port=3307/tcp --permanent

? 重新載入

[root@~]# firewall-cmd --reload

總結(jié)

以上所述是小編給大家介紹的CentOS7.5使用mysql_multi方式安裝MySQL5.7.28多實(shí)例，希望對(duì)大家有所幫助，如果大家有任何疑問(wèn)請(qǐng)給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)服務(wù)器之家網(wǎng)站的支持！如果你覺(jué)得本文對(duì)你有幫助，歡迎轉(zhuǎn)載，煩請(qǐng)注明出處，謝謝！

原文鏈接：https://blog.51cto.com/8355320/2466817