Zero-based MYSQL02---mysql startup mode and troubleshooting, multi-instance Setup

Source: Internet
Author: User
Tags create directory new set mysql login

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.