Multiple instances
In production, with the updating of database version or other requirements, it may be necessary to install multiple databases of different versions (or the same version) on one host. (The database here refers to an entire database separated by the database port number, rather than referring to the database in a database, pay attention to the distinction.)
Here we need to use multiple instances to implement it. Its implementation logic is as follows:
Multiple examples of the same version of Mysql database:
Analysis
First of all, if it's the same version of the database, we only need the application and the main body of the database service (i.e., the various files under / usr/local/mysql in the previous article). Multiple examples are actually the storage location of multiple databases (equivalent to multiple different folders) and their respective configuration files (such as the specified port number). Specify database storage location, socket file, etc.)
It is equivalent to a mysql application service program that opens multiple processes in memory. Each process is a database service without interfering with each other. It has its own database storage directory and its own configuration file. Just because it's the same version of the database, these multiple instances only need to share a set of service principal files.
These maysql services are independent when loaded into memory, because when loaded into memory, the process will be opened according to the configuration file. After loading, all kinds of files in the service body will no longer affect the process, and the process of the database will only modify the contents of the database directory. (equivalent to opening more notebooks in windows, without interference with each other, modifying and saving data independently; opening a new process and independent memory and data space, but mysql where the data space is the database storage directory, that is, an example)
The same version of multi-instance implementation of the test process:
Realize three different instances (three databases, three listening ports, three database services started at the same time). Take mysql which has been installed in binary system as an example, the others are similar.
Note that a mysql service needs to be installed before the following examples. The previous installation process follows the process of the previous chapter step by step, which is not discussed here.
-
Create folder directories for three instances:
mkdir /data/mysql/{3306,3307,3308}/{data,etc,socket,bin,log,pid} -pv -
Modify the subordinate group of this general folder (the previous blog said that only database properties, which is equivalent to / data/mysql/data directory, need to be changed, but now it is more convenient for MySQL users to directly modify the entire subordinate group / data/mysql)
chown -R mysql.mysql /data/mysql -
Then generate the database file (using the generated database script)
- If it's a yum installation, because it's under / usr/bin, you can execute the mysql_install_db --datadir=/data/mysql/3306/data --user=mysql command directly (the data directory of the other two instances can be executed twice as well).
- If it is a binary and compiler installation, pay attention to the script folder scripts directory in the upper directory to find the bin directory, in order to generate the database to the data
-
Refer to the configuration file of / etc/my.cof system itself for modification and put it into the etc directory of each instance (only 3306 is written here, the other two modifications can be done), pay attention to adding a port number port=
- For binary installation or source code compilation, you can also refer to my-big.cnf file in support-file, but for convenience, you can modify it according to / etc/my.cof.
- One thing to note, however, is that mysqld starts in the order of configuration files when it starts, because the self-defined / data/mysql/330X/etc folder is not in the default search path of the mysqld service startup, so it will also use the configuration in effect with / etc/my.cof (unless there is a higher priority cof file later). So we need to pay attention to it.
- The target database directory, socket file directory, log file directory and pid directory all need to be modified to their respective examples (if written in my.cof)
- This configuration can be written a lot, including the configuration file in binary installation. My-big.cnf file does not specify the location of the log file in [mysqld_safe]. The default self-contained file is specified, so it is better to combine the two to make a common configuration file whether it is yum, binary or source code.
- Look more at the analysis below.
=======================================================Default items: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock symbolic-links=0 [mysqld_safe] log-error=/var/log/mariadb/mariadb.log pid-file=/var/run/mariadb/mariadb.pid !includedir /etc/my.cnf.d =======================================================The revised project: [mysqld] port=3306 datadir=/data/mysql/3306/data socket=/data/mysql/3306/socket/mysql.sock symbolic-links=0 [mysqld_safe] log-error=/data/mysql/3306/log/mariadb.log pid-file=/data/mysql/3306/pid/mariadb.pid [client] #password = your_password port = 3306 #socket = /data/mysql/mysql.sock socket=/data/mysql/3306/socket/mysql.sock !includedir /etc/my.cnf.d
- Create service script files for different port numbers:
- You can duplicate three following service script files with different names (such as mysql330X), modify the configuration inside (with comments) and start them by creating services under / etc/init.d (the system CLT status command will display errors at this time, but the service has actually started)
- Or copy them separately under each / data/mysql/330X/bin, and specify absolute path to start directly at boot time, instead of adding them to service service service.
Only 3306 ports are listed below, and the other two are similar to each other: port 3306, port 3306, port 3306, port 3306, port 3306, port 3306, port 3306, port 3306, port 3306,
Additional Knowledge Points: Modifying passwords can be modified with mysqladmin and - S specifying sockets. When connecting to these databases, you can also use mysql -S specifying sockets to connect, or specify host(ip) and port number connections (at this point, through network connections, you need to set permission in user).
Change to the items to be added in the service startup: (the startup sequence 64,36 can be written in the same way, as long as the name of the service script file is changed to inconsistent, it will be sorted automatically). After adding, it can be started in the service MySQL 330X start mode: #!/bin/bash: Bash instead # Comments to support chkconfig on RedHat Linux # chkconfig: 2345 64 36 # description: A very fast and reliable SQL database engine.
Start script:
#!/bin/bash
port=3306
# mysql_user="root": This includes the next item for closing the service for mysqladmin. It's better not to do this, because the user name and password are written in plaintext.
# mysql_pwd=""
cmd_path="/usr/local/mysql/bin" #Write down the location of bin where you installed mysqld, and yum is directly / usr/bin: it is used for multiple instances to use mysql's main program script, etc., without having to install the same version of MySQL multiple times
mysql_basedir="/data/mysql"
mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock"
pid_file="${mysql_basedir}/${port}/pid/mariadb.pid"
function_start_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "Starting MySQL...\n"
${cmd_path}/mysqld_safe --defaults-file=${mysql_basedir}/${port}/etc/my.cnf &> /dev/null &
else
printf "MySQL is running...\n"
exit
fi
}
#function_stop_mysql()
#{
# if [ ! -e "$mysql_sock" ];then
# printf "MySQL is stopped...\n"
# exit
# else
# printf "Stoping MySQL...\n"
# ${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown
# fi
#}
#Similar to referencing the original script to shut down the service, no password, etc.
function_stop_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "MySQL is stopped...\n"
exit;
elif [ -e "$pid_file" ];then
read mysqld_pid < "$pid_file"
printf "Stoping MySQL...\n"
kill $mysqld_pid;
exit;
else
cd "${mysql_basedir}/${port}/data/"
read mysqld_pid < "${mysql_basedir}/${port}/data/`ls *.pid`"
printf "Stoping MySQL...\n"
kill $mysqld_pid;
exit;
fi
}
function_restart_mysql()
{
printf "Restarting MySQL...\n"
function_stop_mysql
sleep 2
function_start_mysql
}
case $1 in
start)
function_start_mysql
;;
stop)
function_stop_mysql
;;
restart)
function_restart_mysql
;;
*)
printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n"
esac
- At this time, the client command mysql connection can be used. Note that the configuration files of the client command mysql and the server mysqld can be written together, but they are different when used. You can use mysql --help to view the order and location of the client command mysql configuration file (as described in the previous lines)
- You can connect with mysql-uroot@HOST-pPWD-S/data/mysql/3307/socket/mysql.sock
- You can also use mysql-uroot@host-pPWD -- defaults-extra-file=/data/mysql/3308/etc/my.cnf (provided the port number and socket file location for client connections are written in this file, see the [mysql] column in 4)
- At present, there are security problems with the default settings, but security hardening scripts are no longer available, so:
- If you need to modify the password, use the mysqladmin command. Note that in a similar way in 6, specify the socket file of - S to modify the corresponding user name password (provided that the server is activated first, the service is not available without starting this command).
- As for the deletion of users who log in anonymously and connect directly remotely, the corresponding rows in user table in mysql database can be deleted directly by delete command.
Here we go on with the analysis in step 4 above.
- It should be noted that when binary installation, because the reference my. big. cfg file is compiled on the official website, it puts the socket file (unix socket) under / tmp/mysql.sock. The sock files that are eventually generated and used are also in this directory.
- The yum installation and source code compilation installation are in / var/lib/mysql/mysql.sock and / data / MySQL / mysql. sock respectively (under the home directory folder specified by the source code at that time)
- However, after testing, we know that this sock file is generated by the configuration file (note that both client and server columns specify socket), ++ by restart service command, rather than by mysql_install_db script. It is only a temporary file, when the server is active, it will delete ++,
- More tests show that the log files (including the default database files) are also automatically generated by restarting the mysqld service (service command) according to the configuration file under the [mysqld] column in my.cnf file.
- This is because the script [mysqld_safe] written in the above configuration specifies the location of two log files, which will be automatically generated by the mysqld_safe script (because the script is launched when the service service service is started at the time of binary installation or compilation installation).
- Through the help information options of mysql_install_db, you can also see some clues, indicating that there are many configuration files, and the options are also scattered in these different files.
- Here are some of the test results:
In the installation environment of yum, according to the premise that the configuration file does not write anything (the configuration file is empty due to the installation of yum), after deleting all the files under the database folder / var/lib/mysql of Yum installation, the contents of the files generated with the command of mysql_install_db are as follows:
==============================================================Delete the default before: 23:24[root@centos7 /var/lib/mysql]# ls aria_log.00000001 centos7.pid ibdata1 ib_logfile1 multi-master.info mysql.sock tc.log aria_log_control ib_buffer_pool ib_logfile0 ibtmp1 mysql performance_schema test ==============================================================Regenerated after deletion: 23:34[root@centos7 /var/lib/mysql]# ls aria_log.00000001 aria_log_control ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 mysql performance_schema test
However, after restarting the service, system CTL restart MySQL restores all previous data, including sock files:
Combined with the analysis in the following section, it should be known that mysql using yum and systemctl should write these configuration files elsewhere (possibly in unit.service)
00:05[root@centos7 /var/lib/mysql]# ls aria_log.00000001 centos7.pid ibdata1 ib_logfile1 multi-master.info mysql.sock tc.log aria_log_control ib_buffer_pool ib_logfile0 ibtmp1 mysql performance_schema test
By looking at / usr / lib / system MD / system / mysqld. service in rpm -ql mariadb-server installed by yum, you can see that the definition location of variables is also written in the annotations (below)
# Start main service # MYSQLD_OPTS here is for users to set in /etc/systemd/system/mariadb.service.d/MY_SPECIAL.conf # Use the [Service] section and Environment="MYSQLD_OPTS=...". # This isn't a replacement for my.cnf. # _WSREP_NEW_CLUSTER is for the exclusive use of the script galera_new_cluster ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION
- Note that the mysqld service installed by Yum in centos7 is started by system CTL by default. It is different from binary installation (or source installation) that uses service to start. There are unit.service files installed directly with yum, but there are no units. service files installed by secondary system and source code.
The following are the differences in service status between yum installation and binary installation:
Note: However, there is also an installation package on the official website that indicates the use of the version of system D. If it is used for binary installation (or source installation), it may be able to start with system D. There is no further test here, and there is time for further study.
==============================================================================YUM ( systemd):
23:50[root@centos7 /var/lib/mysql]# systemctl status mysql
● mariadb.service - MariaDB 10.3.14 database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
Drop-In: /etc/systemd/system/mariadb.service.d
└─migrated-from-my.cnf-settings.conf
Active: active (running) since Tue 2019-04-16 23:15:16 CST; 36min ago
Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/
Process: 23719 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 23675 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
Process: 23672 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Main PID: 23687 (mysqld)
Status: "Taking your SQL requests now..."
CGroup: /system.slice/mariadb.service
└─23687 /usr/sbin/mysqld
Apr 16 23:15:16 centos7.6test mysqld[23687]: 2019-04-16 23:15:16 0 [Note] InnoDB: 10.3.14 started; log sequence number 1630815; transaction id 21
Apr 16 23:15:16 centos7.6test mysqld[23687]: 2019-04-16 23:15:16 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
Apr 16 23:15:16 centos7.6test mysqld[23687]: 2019-04-16 23:15:16 0 [Note] InnoDB: Buffer pool(s) load completed at 190416 23:15:16
Apr 16 23:15:16 centos7.6test mysqld[23687]: 2019-04-16 23:15:16 0 [Note] Plugin 'FEEDBACK' is disabled.
Apr 16 23:15:16 centos7.6test mysqld[23687]: 2019-04-16 23:15:16 0 [Note] Server socket created on IP: '::'.
Apr 16 23:15:16 centos7.6test mysqld[23687]: 2019-04-16 23:15:16 0 [Note] Reading of all Master_info entries succeded
Apr 16 23:15:16 centos7.6test mysqld[23687]: 2019-04-16 23:15:16 0 [Note] Added new Master_info '' to hash table
Apr 16 23:15:16 centos7.6test mysqld[23687]: 2019-04-16 23:15:16 0 [Note] /usr/sbin/mysqld: ready for connections.
Apr 16 23:15:16 centos7.6test mysqld[23687]: Version: '10.3.14-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
Apr 16 23:15:16 centos7.6test systemd[1]: Started MariaDB 10.3.14 database server.
========================================================================================Binary( init.d The following service script)
20:27[root@centos7 /usr/local/mariadb-10.2.23-linux-x86_64/support-files]# systemctl status mysqld.service
● mysqld.service - LSB: start and stop MariaDB
Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
Active: active (running) since Mon 2019-04-29 17:58:44 CST; 1 day 2h ago
Docs: man:systemd-sysv-generator(8)
Process: 8290 ExecStop=/etc/rc.d/init.d/mysqld stop (code=exited, status=0/SUCCESS)
Process: 8356 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=0/SUCCESS)
CGroup: /system.slice/mysqld.service
├─8420 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/centos7.6test.pid
└─8556 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysq...
Apr 29 17:58:43 centos7.6test systemd[1]: Starting LSB: start and stop MariaDB...
Apr 29 17:58:43 centos7.6test mysqld[8356]: Starting MariaDB.190429 17:58:43 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
Apr 29 17:58:43 centos7.6test mysqld[8356]: 190429 17:58:43 mysqld_safe Starting mysqld daemon with databases from /data/mysql
Apr 29 17:58:44 centos7.6test mysqld[8356]: SUCCESS!
Apr 29 17:58:44 centos7.6test systemd[1]: Started LSB: start and stop MariaDB.
From ps aux |grep mysql, you can see what these variables are and what the parameters of the mysqld service are.
The same yum installation uses the system D service and the custom installation does not use the system D service, but the service service service is different:
- The following is binary. You can see that the parameters of the main service mysqld are started through the mysqld_safe script.
root 8420 0.0 0.0 115436 1748 ? S 17:58 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/centos7.6test.pid mysql 8556 0.0 7.5 2008736 140732 ? Sl 17:58 0:10 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/log/mariadb/mariadb.log --pid-file=/data/mysql/centos7.6test.pid --socket=/tmp/mysql.sock --port=3306 root 14724 0.0 0.0 112708 976 pts/0 S+ 22:08 0:00 grep --color=auto mysql
Looking at the startup script under init.d, we can see that:
The mysqld service starts with a $bindir/mysqld_safe script in the bin directory, which is supposed to be encrypted by the individual, and then starts the mysqld main service program.
ase "$mode" in
'start')
# Start daemon
# Safeguard (relative paths, core dumps..)
cd $basedir
echo $echo_n "Starting MariaDB"
if test -x $bindir/mysqld_safe
then
# Give extra arguments to mysqld with the my.cnf file. This script
# may be overwritten at next upgrade.
$bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" "$@" &
wait_for_ready; return_value=$?
# Make lock for RedHat / SuSE
if test -w "$lockdir"
then
touch "$lock_file_path"
fi
exit $return_value
else
log_failure_msg "Couldn't find MariaDB server ($bindir/mysqld_safe)"
fi
;;- The following is the installation of yum, which is started directly by mysqld without any additional parameters (these parameters should be written in the / usr / lib / system / mariadb. service file)
23:51[root@centos7 /var/lib/mysql]# ps aux | grep mysql mysql 23687 0.0 4.7 1764724 88244 ? Ssl Apr16 0:02 /usr/sbin/mysqld root 25024 0.0 0.0 112708 980 pts/0 S+ 00:00 0:00 grep --color=auto mysql
- At present, it is not clear whether this / usr/sbin/mysqld file installed by Yum in centos7 has written the configuration (according to the configuration defined by yum, such as mysqld service main body directory, database directory, etc., referring to the parameters after mysqld launched by service), so mysqld service started by systemclt does not need to write parameters anymore?
- The service command of the yum installation can also start (how to start is described below), but the parameters are redundant (these parameters should be added when the safe script starts mysqld, but it is not clear where these parameters are written, it may be judged and defined in safe, because my.cnf of the yum installation is empty, so the parameters are written elsewhere. There are also.
- The guess is that if all the configuration files are empty, the service will be opened according to the default parameters written in safe, which are written directly in safe; and if the configuration files exist and contain content, which is not empty, the parameters will be added according to the configuration files, which is just in line with the logical and practical test results: that is, the configuration text. More parameters can be added to modify the parameters defined by default, which must be written somewhere, but do not know where to write, guess in the safe script or directly in the mysqld binary file)
- After testing and checking, it is known that the package installed by yum also contains service script files. It can be directly entered into init.d folder and then started in the way of. / mysql start (service mysql start can not be used, because this command will be directly transferred to system CTL command, not service command), and can also be started in a binary manner.
- And at this point, using ps aux | grep mysql is the same as when the binary installation was started, not just as described in the previous 2. However, after booting, you can see that it is only reload state by using system CTL status MySQL at this time, but it has actually started.
- At this point, if the system CTL command is started again, a process in 2 (mysqld service but no parameters) will be opened again, resulting in a jam (two conflicts), so the service must be restarted after kill ing these service processes.
- This shows that both service and system CTL can be started, but there will be conflicts.
- At the same time, on centos7, the default service startup installed with yum points directly to the system CTL command, but the service command installed with binary (or source) even on centos7 and 6 do not point to the system CTL command. This also needs attention.
00:48[root@centos7 ~]# cd - /etc/init.d 00:48[root@centos7 /etc/init.d]# ./mysql start Starting MariaDB.190417 00:48:17 mysqld_safe Logging to '/var/lib/mysql/centos7.6test.err'. 190417 00:48:17 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql SUCCESS! 00:48[root@centos7 /etc/init.d]# ps aux | grep mysql root 28345 0.1 0.0 113312 1656 pts/1 S 00:48 0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/centos7.6test.pid mysql 28414 2.4 4.3 1766812 81884 pts/1 Sl 00:48 0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/centos7.6test.err --pid-file=/var/lib/mysql/centos7.6test.pid root 28449 0.0 0.0 112708 980 pts/1 S+ 00:48 0:00 grep --color=auto mysql 00:48[root@centos7 /etc/init.d]#
rm -rf /data/mysql/*
This command deletes all the information in the database and then clears the root password (violence), but only when tested.
Then you can reinstall the database with the mysql_instal_db command. Looking at its help at this time, you can see more options to partially set up the database.
Then restart the database service, which generates the mysql.sock file (written according to the configuration file)
Troubleshooting:
In order to implement multi-instance scripts, it is unreasonable to use mysqladmin to close mysql service without entering passwd in the startup script.
Want to refer to the service's own writing
Error: After modifying the init.d/mysqld service script, the mysqld service is still booting because it causes problems later. The system CTL command in centos7 prompts me to change mysqld. service on disk, requiring daemon-reload into memory. So after deamon-reload, mysqld service can't be started anyway.
Additional Knowledge Point 1: System CTL is also used to control service command in centos7. You can use system CTL to show mysqld. service to see its startup parameters. From here, you can see that the system CTL command eventually generates a / run / system D / generator. late / mysqld. service service service script for command startup.
The error message indicates that kill-0 cannot find the PID of the last command running in the background in the wait_for_read function (using the parameter $! Which represents the last process PID running in the background of the current shell).
In the start project of the / init.d/mysqld script, you can see that the line of mysqld_safe command is run in the background, and the next step is the wait_for_read function.
To detect the cause of the error, when you start the mysqld_safe command manually (without & background boot), you find that it can't always start (it won't get stuck, and without the card owner, it means that the startup failed because it didn't add &)
At this time, because of the location of the log-error file written in the configuration file, (note that if the parent directory of the log file location does not exist, it can not be created), a log-error file is created, from which we can see that it does not create another PID file location (two cases, one is the wrong location of the file, the parent directory does not exist can not be created; the other is the service does not exist; There's a boot, of course there won't be a pid)
By looking at this file, we can see that the reason why the sock file can not be started is because the sock file is not clean, because the sock is still starting when the service is changed above. At this point, just modify the socket location in the configuration file or delete the original sock. Then restart the service (sock was restarted service generation as mentioned earlier)
Additional Knowledge 2: See the logic of the service script wait_for_read in / init.d/mysqld, which is to wait for the mysqld_safe command to fully start and start mysqld, until the connection state is allowed (there is a time threshold, which has been circulating in it), using the parameter of $!
Additional Knowledge 3: If the log-error location is not written in the configuration file, the default location is in the database directory. If it's written, it's not. If you comment out or delete it again after writing, restart the service and it becomes the default. It does seem like the logic of the previous blog analysis. All variables and parameters have default configuration. I don't know where to write them. I will only change them if I write them in the configuration file.
23:11[root@centos7 /data/mysql]# mysqladmin variable |grep error | log_error | /data/mysql/centos7.6test.err 23:11[root@centos7 /data/mysql]# Vim/etc/my.cnf: Modify the location of the log file and uncomment it 23:12[root@centos7 /data/mysql]# service mysqld restart Restarting mysqld (via systemctl): [ OK ] 23:12[root@centos7 /data/mysql]# mysqladmin variable |grep error | log_error | /data/mariadb.log