1. MySQL's Way and difference
How to troubleshoot when MySQL boot encounters a problem/etc/init/d/mysql Start | Restart | Stop this script is generally installed after the automatic generation by mysql.server, if it is directly decompressed using the need to manually add.
set MySQL auto-start: chkconfig MySQL on, if the online library, I suggest to turn off automatic start, online library in line with a principle, the error stopped. or scripts to control the start and stop of batches. /etc/init.d/mysql (mysql.server)/usr/local/mysql/bin/mysqld_safe--defaults-file=/etc/my.cnf &/usr/local/m Ysql/bin/mysqld--defaults-file=/etc/my.cnf & Start: Mysql-s/tmp/mysql3302.sock off: mysqladmin-s/tmp/mys Ql3376.sock shutdown
Mysql.server calls the mysqld of the Mysqld_safe,mysqld_safe call,A
group of MySQL with a single port number, the port number between all groups is best not to repeat.
2. Create a new set of servers on multiple instances: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
get ready for work finish start initialization: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 & Start: Mysql-s/tmp/mysql3302.sock Close: mysqladmin-s/tmp/mysql3377.sock shutdown
basic ideas for creating multiple instances:Create the base directory and modify permissions, copy the configuration file, replace the port number, initialize, start, close.
multiple instances can be written in the same my.cnf, but for subsequent management maintenance it is convenient to use separate my.cnf for a single instance as far as possible
How to start the 3.mysqld_mutli:Mysqld_multi can call Mysqld_safe, or mysqld can be called, but if Mysqld_multi, it is recommended to call Mysqld_safe,
the Mysqld_multi configuration file must be/etc/my.cnf. [mysqld_multi] mysqld=/usr/ local/mysql/bin/mysqld_safe mysqladmin =/usr/local/mysql/bin/mysqladmin #用来做关闭mysql使用 user = root &N Bsp 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
single-machine multi-instance in the configuration file should be aware of what the problem:Port Port=3377datadir socket=/tmp/mysql3377.sock Socket Datadi R=/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
of the mysqld configuration file
Load Order:/ETC/MY.CNF/ETC/MYSQL/MY.CNF/USR/LOCAL/MYSQL/ETC/MY.CNF ~/.my.cnf Help Documentation: MSQYLD--verbose--help Install errors when you want to see the error log, Is the error log, all the problems will be recorded here SELinux must be banned, in the production environment also, if the company has a hardware firewall, it is best to also iptables off, if not, control the iptables open port ibdata1 size algorithm: 6400* 16/1024, if your ibdata1 become very large it is possible to enable the sharing table space, shared table space, even after the data is cleaned up, will not reclaim space, this How to handle: 1. Dump out 2. Enable standalone table space Import if it's smaller, can you start it auto_extend
two-instance my.cnf actual configuration:
upload binary files to/usr/local/&& mkdir-p/software/mysql decompression: TAR-XZVF mysql-5.6.29-linux-glibc2.5-x86_64.tar.gz MV mysql-5.6.29 -linux-glibc2.5-x86_64 MySQL
create user, create MySQL user and group, set MySQL login password: Groupadd mysql useradd-g mysql-d/home/mysql mysql-d means that the specified home can use man to view useradd usage changing password For user mysql. New password:mysql Create directory: mkdir-p/data/mysql/mysql3376/{data,logs} mkdir -p/data/mysql/mysql3377/{data,logs} Authorized: Chown-r mysql:mysql/data/mysql/mysql3376/ chown-r mysql:mysql/data/mysql/mysql3377/ creating parameter files in mysql3376 and mysql3377 directories, respectively VI my3376.cnf [client] Port = 3376socket =/tmp/mysql.sock # the mysql server[mysqld]# basicport = 3376user = Mysqlbasedir =/usr/local/mysq L # #二进制安装文件存放路径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 = # #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 # # # Ali set to 1innodb_lock_wait_timeout = 50innodb_sync_spin_loops = 100innodb_max_dirty_pages_pct = 75innodb_thread_ concurrency = 0 # # # #5.6.29 The default value is 16, and Ali RDS inside is set to 0,0 to indicate unrestricted Innodb_thread_sleep_delay = 1000innodb_concurrency_tickets = 1000log_bin_trust_function_creators = 1innodb_flush_method = o_directinnodb_file_per_table = 1 set independent table empty 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 Instance parameters The following parameters need to be modified to be different from the 3376 port DataDir Socket Server-id Binlog named cd/data/mysql/mysql3377/cp/data/mysql/mysql3376/my3376.cnf my3377.cnfsed-i The last parameter of ' s/3376/3377/g ' my3377.cnf is #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 # # # Set the character set Slow_query_log =1long_query_time = 1binlog_format = ROW # # #binlog格式max_binlog_size = 512mbinlog_cache_size = 2mexpire-logs-days = # #binlog日志过期天数lower_case_table_names =1 # #不区分大小写back_log = 3000max_connections=1000 # # # Set maximum number of connections 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 # # # Ali set to 1innodb_lock_wait_timeout = 50innodb_sync_spin_loops = 100innodb_ max_dirty_pages_pct = 75innodb_thread_concurrency = 0 # # # #5.6.29 The default value is 16, and Ali RDS is set to 0,0 to show no limit Innodb_thread_sleep_delay = 1000innodb_concurrency_tickets = 1000log_bin_trust_function_creators = 1innodb_flush_method = O_DIRECTinnodb_file_ per_table = 1 Set stand-alone table empty 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 Database initialization: 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
Zero-based MYSQL02---mysql startup mode and troubleshooting, multi-instance setup
