MySQL startup with multi-instance installation

Starting mode and troubleshooting one, several questions 1.1/etc/init.d/mysql from where?

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql

1.2 MySQL can be set to boot up

Yes, but not necessary.

# 方法1：chkconfig mysql on# 方法2：echo "/etc/init.d/mysql start" >> /etc/rc.local

1.3 Boot up?

Is there any need to set MySQL to boot up

If the online library, it is recommended to turn off auto-start, online library in line with a principle, the error will stop, with a script batch start

1.4 Configuration file location and load order

Mysqld which locations the configuration file can be loaded

mysql global status | grep my.cnfmysql --help | grep my.cnfmysqld --verbose --help | grep my.cnfman mysqldmysqld --verbose --help | grep defaults-file--defaults-file=#       

1.5 How MySQL is started

# 1/etc/init.d/mysql start# 2（1与2都是调用的mysql.server）service mysql start# 3/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &# 4/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf &# mysql.server调用的mysqld_safe# mysqld_safe调用的mysqld (mysqld_safe是mysqld的守护进程，会自动重启)# 建议用如下启动方式，一机多实例启动也就简单了# mysqld_safe启动，mysqld进程挂掉，会自动重启，不便于定位问题/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf &/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3376/my3376.cnf &/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3377/my3377.cnf &# 关闭/usr/local/mysql/bin/mysqladimn -S /tmp/mysql3376.sock shutdown

Second, why recommended to start with mysqld

1. Mysqld_safe is the guardian of the mysqld process.
2. One-machine multi-instance start-up is simple.
3. Mysqld_safe start, mysqld process hangs, will automatically restart, not easy to locate the problem

Three or one-machine multi-instance 3.1 scene

1. Prevent two business impact, each business needs a small amount of resources, 2, 3G of memory is enough

2. MySQL decreases as the number of connections increases, reducing the number of connections by splitting the instance

3. Different port numbers run different business

3376->userdb

3377->cmsdb

3.2 Tips to reduce misoperation

A group of MySQL with a unique port number, do not repeat between all groups

Such as: a set of corresponding one master two from

3.3 implementation

1. 创建目录mkdir -p /data/mysql/mysql3377/{data,tmp,logs}2. 修改配置文件，端口# cp /data/mysql/mysql3376/mysql3376.cnf /data/mysql/mysql3377/mysql3377.cnf# cp /etc/my.cnf /data/mysql/mysql3377/mysql3377.cnf# sed -i 's/3376/3377/g' /data/mysql/mysql3377/mysql3377.cnfcp /etc/my.cnf /etc/mysql3377.cnfsed -i 's/3376/3377/g' /etc/mysql3377.cnf3. 修改权限sudo chown -R mysql:mysql /data/mysql/mysql33774. 初始化，指定配置文件cd /usr/local/mysql# ./scripts/mysql_install_db --defaults-file=/data/mysql/mysql3377/mysql3377.cnfsudo ./scripts/mysql_install_db --defaults-file=/etc/mysql3377.cnf5. 通过mysqld启动# mysqld --defaults-file=/data/mysql/mysql3377/mysql3377.cnf &mysqld --defaults-file=/etc/mysql3377.cnf &6. 安全加固mysql -S /tmp/mysql3377.sock7. 关闭mysqladimn -S /tmp/mysql3377.sock shutdown# 可以写成脚本，只需要如下3个参数port, bp size, appname$appname$port

Iv. Mysqld_multi

1. Mysqld_multi is a single configuration file to implement multiple instances of a machine
2. Mysqld_multi can call Mysqld_safe, or you can call mysqld
3. If Mysqld_multi, it is recommended to call Mysqld_safe
4. MY.CNF must be in the/ETC/MY.CNF position.
5. Http://www.cnblogs.com/LCX/archive/2010/04/02/1703215.html

4.1 To my.cnf append the following content

Must be/ETC/MY.CNF This default location of the configuration file

[Mysqld_safe] Parameters are intended for use by Mysqld_safe programs only

[MYSQLDN] will overwrite [mysqld] part, the prefix must be mysqld

Port/datadir/socket must not be the same

[mysqld_multi]mysqld = /usr/local/mysql/bin/mysqld_safemysqladmin = /usr/local/mysql/bin/mysqladmin# 用来做关闭mysql使用user = root# password = 123456log = /data/mysql/mysqld_multi.log[mysqld3378]socket = /tmp/mysql3378.sockport = 3378datadir = /data/mysql/mysql3378/datauser = mysqlpid-file = /data/mysql/mysql3378/data/mysql3378.pidlog-error = /data/mysql/mysql3378/logs/error.loglog-bin = /data/mysql/mysql3378/logs/mysql-bininnodb_data_home_dir = /data/mysql/mysql3378/datainnodb_log_group_home_dir = /data/mysql/mysql3378/logs [mysqld3379]socket = /tmp/mysql3379.sockport = 3379datadir = /data/mysql/mysql3379/datauser = mysqlpid-file = /data/mysql/mysql3379/data/mysql3379.pidlog-error = /data/mysql/mysql3379/logs/error.loglog-bin = /data/mysql/mysql3379/logs/mysql-bininnodb_data_home_dir = /data/mysql/mysql3379/datainnodb_log_group_home_dir = /data/mysql/mysql3379/logs

4.2 Start

# 查看MySQL状态/usr/local/mysql/bin/mysqld_multi report# 前提是3378和3379的数据库文件已经初始化完了cd /usr/local/mysql./scripts/mysql_install_db --defaults-file=/etc/mysql3378.cnf./scripts/mysql_install_db --defaults-file=/etc/mysql3379.cnf# 启动，不加参数是启动所有实例/usr/local/mysql/bin/mysqld_multi start# 只启动3376，通过端口号指定/usr/local/mysql/bin/mysqld_multi start 3376# 关闭，不加参数是关闭所有实例mysqld_multi stopmysqld_multi stop 3376mysqladmin shutdown -S /tmp/mysql3376.sock

4.3 Single-run multi-instance

# 以下三个绝对不能一样portdatadirsocket

Server-id

Five, packaging initialization

/data/mysql/mysql3376# 打包前，删除auto.cnfrm -rf /data/mysql/mysql3376/auto.cnftar -czvf mysqldata.tar.gz /data/mysql/mysql3376

VI. Troubleshooting 6.1 See error log

# my.cnf定义错误日志的输出log-error = /data/mysql/mysql3376/logs/error.logcat /data/mysql/mysql3376/logs/error.log

6.2 Dictionary Data too large

# 数据字典文件innodb_data_file_path=ibdata1:100M:autoextend# 不能改大，改小了可以

6.3 Ibdata1 Very Large

Ibdata1 is very big how to do?

It is possible that the shared table space is enabled and changed to a separate table space

Shared table space, that is, after the data is cleaned out, will not reclaim space

Solve:

1. Dump out.
2. Enable standalone table space Import

6.4 Optimization Errors

[mysqld_safe]malloc-lib = /usr/local/mysql/lib/mysql/libjemalloc.so

With/etc/init.d/mysql and Mysqld_safe, even the error log is not written

The error log is written by mysqld, so even mysqld is not called to

Solve:

1. Find the missing files, compile and put them in.
2. Comment out this sentence optimization

6.5 User who started the MySQL

2017-11-18 12:52:04 4766 [ERROR] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!

Start MySQL as MySQL------that's not the reason.

# su - root -c "command"mysqld --defaults-file=/data/mysql/mysql3377/mysql3377.cnf &su - mysql -c "mysqld --defaults-file=/data/mysql/mysql3377/mysql3377.cnf &"

MY.CNF do not let other users have W permissions

6.6 Setting Error_log

Error_log must be created in advance.

https://bugs.mysql.com/bug.php?id=84427

6.7 Permissions Error

/tmp directory permissions are incorrect

/TMP is a special directory that can be mistakenly manipulated during daily operations.

Iptables

Firewalld

SELinux

getenforce

Vii. Summary of 7.1 sources:

Directory Permissions Issues
Optimization issues

7.2 Solution Ideas:

Look at the call relationship
Look at the log

7.3 Requirements

1. You can follow the requirements of a database 3378
2. Understanding the invocation relationships of several startup methods
3. Understanding the order in which configuration files are loaded
4. Master the troubleshooting ideas of startup failure
5. Multi-Instance Deployment

MySQL startup with multi-instance installation