In this manual, we will run multiple Mysql databases on a single physical server using the installed version of Mysql-5.5. Running multiple Mysql servers on a single server can be necessary in order to run replication on one, or give access to someone (programmer, friend, client) to a separate clean Mysql database. The database is configured on the Debian GNU/Linux operating system.
We need to create a new directory for data and file logs. You also need to assign the right rights to these folders:
mkdir /var/lib/mysql2 chown -R mysql:mysql /var/lib/mysql2/ mkdir /var/log/mysql2 chown -R mysql:mysql /var/log/mysql2
We need to create a new configuration file. We can copy an existing config and change the necessary values. In my Debian distribution, the configuration file is in /etc/mysql/my.cnf:
cp -R /etc/mysql/ /etc/mysql2
Next, we need to edit the configuration file and make changes to at least the parameters. mysql port (default 3306), pid and socket, and also directories of data and logs:
sed -i 's/3306/3307/g' /etc/mysql2/my.cnf sed -i 's/mysqld.sock/mysqld2.sock/g' /etc/mysql2/my.cnf sed -i 's/mysqld.pid/mysqld2.pid/g' /etc/mysql2/my.cnf sed -i 's/var\/lib\/mysql/var\/lib\/mysql2/g' /etc/mysql2/my.cnf sed -i 's/var\/log\/mysql/var\/log\/mysql2/g' /etc/mysql2/my.cnf
Initialize a clean database:
mysql_install_db --user=mysql --datadir=/var/lib/mysql2/
Start a new copy of mysql server:
mysqld_safe --defaults-file=/etc/mysql2/my.cnf &
You can connect to it using commands:
mysql -S /var/run/mysqld/mysqld2.sock
or:
mysql -h 127.0.0.1 -P 3307
If the work of this base is no longer needed, stop it by executing the command:
mysqladmin -S /var/run/mysqld/mysqld2.sock shutdown
Use "127.0.0.1" instead of "localhost". Otherwise, instead of connecting to the desired port, a socket will be used by default. This is not the same server we created.
Create a password. root For the second version of mysql database, we will perform a command by replacing pass_mysql with your database password:
mysqladmin --host=127.0.0.1 --port=3307 -u root password 'pass_mysql'
Connect to the database using the root password:
mysql -h 127.0.0.1 -P 3307 -u root -ppass_mysqlTo change the password of the root user of the mysql database by executing a command in the database console:
update user set password=PASSWORD("new_pass") where User='root';Now add the start and stop of the second version of mysql, for this you need to enter in the launch file mysql:
vi /etc/init.d/mysql
was:
...
# Start MySQL!
/usr/bin/mysqld_safe > /dev/null 2>&1 &
...became:
...
# Start MySQL-1!
/usr/bin/mysqld_safe > /dev/null 2>&1 &
# Start MySQL-2!
/usr/bin/mysqld_safe --defaults-file=/etc/mysql2/my.cnf > /dev/null 2>&1 &
...was:
'stop')
# * As a passwordless mysqladmin (e.g. via ~/.my.cnf) must be possible
# at least for cron, we can rely on it here, too. (although we have
# to specify it explicit as e.g. sudo environments points to the normal
# users home and not /root)
log_daemon_msg "Stopping MySQL database server" "mysqld"
if ! mysqld_status check_dead nowarn; then
set +e
shutdown_out=`$MYADMIN shutdown 2>&1`; r=$?became:
'stop')
# * As a passwordless mysqladmin (e.g. via ~/.my.cnf) must be possible
# at least for cron, we can rely on it here, too. (although we have
# to specify it explicit as e.g. sudo environments points to the normal
# users home and not /root)
log_daemon_msg "Stopping MySQL database server" "mysqld"
if ! mysqld_status check_dead nowarn; then
set +e
# Shutdown MySQL-1!
shutdown_out=`$MYADMIN shutdown 2>&1`; r=$?
# Shutdown MySQL-2
mysqladmin -S /var/run/mysqld/mysqld2.sock shutdown -ppass_mysql_root > /dev/null 2>&1
To check, overload mysql database by executing the command:
/etc/init.d/mysql restart
Connect to the second server by executing the command:
mysql -h 127.0.0.1 -P 3307 -u root -ppass_mysql
The second Mysql database server has been started and configured, so you can run the number of servers you need that your physical server can handle.
Sergei Lazarenko was with you.
Let’s take a short break and watch a video of how you can make a fire in deep snow, if not when you need such knowledge :
comment