標籤：
1. MySQL的方式及區別
在MySQL啟動遇到故障怎麼排查 /etc/init/d/mysql start | restart | stop 這個指令碼是一般安裝後由mysql.server 自動產生，如果是直接解壓使用的需要手動添加。
設定mysql自動啟動：chkconfig mysql on，如果線上庫，我建議關閉自動啟動，線上庫本著一個原則，出錯了就停下來。或者指令碼來控制批量的啟動與停止。 /etc/init.d/mysql ( mysql.server) /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf & /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf & 啟動：mysql -S /tmp/mysql3302.sock 關閉：mysqladmin -S /tmp/mysql3376.sock shutdown
mysql.server 調用的mysqld_safe，mysqld_safe調用的mysqld，
一組MySQL用一個唯一個連接埠號碼，所有組之間的連接埠號碼最好不要重複。
2.建立一組伺服器上多執行個體： mkdir -p /data/mysql/mysql3377/{data,logs,tmp} cp /data/mysql/mysql3376/my3376.cnf /data/mysql/mysql3377/my3377.cnf cd /data/mysql/mysql3377 chown -R mysql:mysql mysql3377 sed -i ‘s/3376/3377/g‘ my3377.cnf
準備工作做完開始初始化操作： cd /usr/local/mysql/ ./script/mysql_db_install --defaults-file=/data/mysql/mysql3377/my3377.cnf
OR /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3377/my3377.cnf & 啟動：mysql -S /tmp/mysql3302.sock 關閉：mysqladmin -S /tmp/mysql3377.sock shutdown
建立多執行個體的基本思路：建立基礎目錄並修改許可權，拷貝設定檔，替換連接埠號碼，初始化，啟動，關閉。
多執行個體是可以寫在同一個my.cnf裡面，但是為了後續管理維護方便盡量單獨一個執行個體使用單獨的my.cnf
3.mysqld_mutli 的啟動方式： mysqld_multi 可以調用mysqld_safe,也可以調用mysqld，但是如果mysqld_multi，建議調用mysqld_safe，
mysqld_multi的設定檔必須是 /etc/my.cnf。 [mysqld_multi] mysqld=/usr/local/mysql/bin/mysqld_safe mysqladmin = /usr/local/mysql/bin/mysqladmin #用來做關閉mysql使用 user = root log=/usr/local/mysql/m.log [mysqld3376] port=3376 socket=/tmp/mysql3376.sock datadir=/data/mysql/mysql3376/data server-id=1003376 log-bin=/data/mysql/mysql3376/logs/mysql-bin tmpdir=/data/mysql/mysql3376/tmp innodb_log_group_home_dir = /data/mysql/mysql3376/logs innodb_buffer_pool_size=200M
單機多執行個體在設定檔應該注意什麼問題： port port=3377datadir socket=/tmp/mysql3377.sock socket datadir=/data/mysql/mysql3377/data server-id server-id=1003377 log-bin=/data/mysql/mysql3377/logs/mysql-bin tmpdir=/data/mysql/mysql3377/tmp innodb_log_group_home_dir = /data/mysql/mysql3377/logs
mysqld設定檔的
載入順序：/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf 協助文檔：msqyld --verbose --help安裝有錯誤的時候要會看error log，就是錯誤記錄檔，所有的問題都會記錄在這裡selinux一定要禁，在生產環境中也要這樣，如果公司有硬體防火牆的話最好也將iptables關掉，如果沒有的話就控制好iptables開放的連接埠 ibdata1 大小演算法：6400*16/1024，如果你的ibdata1變得很龐大很有可能是啟用了共用資料表空間，共用資料表空間，即使把資料清理掉後，也不會回收空間，這種怎麼處理： 1. dump出來 2. 啟用獨立資料表空間匯入如果改小了能啟動嗎 可以auto_extend
雙執行個體my.cnf實際配置：
上傳二進位檔案到/usr/local/ && mkdir -p /software/mysql 解壓： tar -xzvf mysql-5.6.29-linux-glibc2.5-x86_64.tar.gz mv mysql-5.6.29-linux-glibc2.5-x86_64 mysql
建立使用者、建立mysql使用者和組， 設定mysql登入密碼： groupadd mysql useradd -g mysql -d /home/mysql mysql -d意思是指定home 可以使用man查看useradd的用法 Changing password for user mysql. New password: mysql 建立目錄： mkdir -p /data/mysql/mysql3376/{data,logs} mkdir -p /data/mysql/mysql3377/{data,logs} 授權： chown -R mysql:mysql /data/mysql/mysql3376/ chown -R mysql:mysql /data/mysql/mysql3377/ 分別在mysql3376和mysql3377目錄下建立參數檔案 vi my3376.cnf [client]port = 3376socket = /tmp/mysql.sock # The MySQL server[mysqld]# Basicport = 3376user = mysqlbasedir = /usr/local/mysql ##二進位安裝檔案存放路徑datadir = /data/mysql/mysql3376/data ##資料檔案存放路徑socket = /tmp/mysql3376.sock log_bin = mysql3376-binlog_error = /data/mysql/mysql3376/logs/error3376.logslow_query_log_file = /data/mysql/mysql3306/logs/slow3376.logskip-external-lockingskip-name-resolvelog-slave-updatesexplicit_defaults_for_timestamprelay_log=mysql3376-relay-logrelay_log_index=mysql3376relay-log-indexserver-id = 337601character_set_server = utf8 ###設定字元集slow_query_log =1long_query_time = 1binlog_format = ROW ###binlog格式max_binlog_size = 512Mbinlog_cache_size = 2Mexpire-logs-days = 15 ##binlog日誌到期天數lower_case_table_names=1 ##不區分大小寫back_log = 3000max_connections=1000 ###設定最大串連數max_user_connections=1000max_connect_errors=900interactive_timeout=7200connect_timeout = 20slave_net_timeout=60max_relay_log_size = 512Mperformance_schema=0 key_buffer_size = 16Mmax_allowed_packet = 1024Msort_buffer_size = 1Mread_buffer_size = 1Mread_rnd_buffer_size = 1Mjoin_buffer_size = 1Mmyisam_sort_buffer_size = 16Mtmp_table_size = 16Mmax_heap_table_size = 64Mquery_cache_type=0query_cache_size = 0bulk_insert_buffer_size = 16Mthread_cache_size = 100thread_stack = 192Kskip-slave-start # InnoDBinnodb_data_file_path = ibdata1:200M:autoextendinnodb_buffer_pool_size = 1024Minnodb_buffer_pool_instances = 8innodb_log_file_size = 1000Minnodb_log_buffer_size = 16Minnodb_log_files_in_group = 3innodb_flush_log_at_trx_commit = 1 ### 阿里設定為1innodb_lock_wait_timeout = 50innodb_sync_spin_loops = 100innodb_max_dirty_pages_pct = 75innodb_thread_concurrency = 0 ####5.6.29預設值為16,阿里RDS裡面設定為0，0表示不限制innodb_thread_sleep_delay = 1000innodb_concurrency_tickets = 1000log_bin_trust_function_creators = 1innodb_flush_method = O_DIRECTinnodb_file_per_table = 1 設定獨立表空innodb_read_io_threads = 4innodb_write_io_threads = 4innodb_io_capacity = 2000innodb_io_capacity_max = 4000innodb_file_format = Barracudainnodb_purge_threads=1innodb_change_buffering=allinnodb_stats_on_metadata=OFF [mysqldump]quickmax_allowed_packet = 1024M [mysql]no-auto-rehashmax_allowed_packet = 1024Mprompt = ‘\[email protected]\h:\p\d mysql> ‘default_character_set = utf8 [mysqlhotcopy]interactive-timeout [mysqld_safe]#malloc-lib= /usr/local/mysql/lib/mysql/libjemalloc.so 3377執行個體參數 以下參數需要修改為和3376不一樣port datadir socket server-id binlog命名 cd /data/mysql/mysql3377/cp /data/mysql/mysql3376/my3376.cnf my3377.cnfsed -i ‘s/3376/3377/g‘ my3377.cnf 最後參數為#more my3377.cnf [client]port = 3377socket = /tmp/mysql.sock # The MySQL server[mysqld]# Basicport = 3377user = mysqlbasedir = /usr/local/mysql ##二進位安裝檔案存放路徑datadir = /data/mysql/mysql3377/data ##資料檔案存放路徑socket = /tmp/mysql3377.sock log_bin = mysql3377-binlog_error = /data/mysql/mysql3376/logs/error3377.logslow_query_log_file = /data/mysql/mysql3307/logs/slow3377.logskip-external-lockingskip-name-resolvelog-slave-updatesexplicit_defaults_for_timestamprelay_log=mysql3377-relay-logrelay_log_index=mysql3377relay-log-indexserver-id = 337701character_set_server = utf8 ###設定字元集slow_query_log =1long_query_time = 1binlog_format = ROW ###binlog格式max_binlog_size = 512Mbinlog_cache_size = 2Mexpire-logs-days = 15 ##binlog日誌到期天數lower_case_table_names=1 ##不區分大小寫back_log = 3000max_connections=1000 ###設定最大串連數max_user_connections=1000max_connect_errors=900interactive_timeout=7200connect_timeout = 20slave_net_timeout=60max_relay_log_size = 512Mperformance_schema=0 key_buffer_size = 16Mmax_allowed_packet = 1024Msort_buffer_size = 1Mread_buffer_size = 1Mread_rnd_buffer_size = 1Mjoin_buffer_size = 1Mmyisam_sort_buffer_size = 16Mtmp_table_size = 16Mmax_heap_table_size = 64Mquery_cache_type=0query_cache_size = 0bulk_insert_buffer_size = 16Mthread_cache_size = 100thread_stack = 192Kskip-slave-start # InnoDBinnodb_data_file_path = ibdata1:200M:autoextendinnodb_buffer_pool_size = 1024Minnodb_buffer_pool_instances = 8innodb_log_file_size = 1000Minnodb_log_buffer_size = 16Minnodb_log_files_in_group = 3innodb_flush_log_at_trx_commit = 1 ### 阿里設定為1innodb_lock_wait_timeout = 50innodb_sync_spin_loops = 100innodb_max_dirty_pages_pct = 75innodb_thread_concurrency = 0 ####5.6.29預設值為16,阿里RDS裡面設定為0，0表示不限制innodb_thread_sleep_delay = 1000innodb_concurrency_tickets = 1000log_bin_trust_function_creators = 1innodb_flush_method = O_DIRECTinnodb_file_per_table = 1 設定獨立表空innodb_read_io_threads = 4innodb_write_io_threads = 4innodb_io_capacity = 2000innodb_io_capacity_max = 4000innodb_file_format = Barracudainnodb_purge_threads=1innodb_change_buffering=allinnodb_stats_on_metadata=OFF [mysqldump]quickmax_allowed_packet = 1024M [mysql]no-auto-rehashmax_allowed_packet = 1024Mprompt = ‘\[email protected]\h:\p(\d) mysql> ‘default_character_set = utf8 [mysqlhotcopy]interactive-timeout [mysqld_safe]#malloc-lib= /usr/local/mysql/lib/mysql/libjemalloc.so 資料庫初始化: cd /usr/local/mysql/ ./scripts/mysql_install_db --defaults-file=/data/mysql/mysql3376/my3376.cnf ./scripts/mysql_install_db --defaults-file=/data/mysql/mysql3377/my3377.cnf
從零開始的MYSQL02 --- MySQL啟動方式及故障排查，多執行個體搭建
