1. Download the installation package and prepare the environment
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz
# Create the following directory (directory location customized, no mandatory) Software path: /opt/mysql/ Data Path: /data/mysql/data/ Binary Path:/data/mysql/binlog/ Communication path: /tmp/mysql/ Used to generate in this directory at database startup sock file chown -R mysql.mysql /opt/mysql chown -R mysql.mysql /tmp/mysql chown -R mysql.mysql /data/*
centos7 Upper Installation mysql before,First bring the system's own mariadb Unload
yum remove mariadb-libs
2. Unzip the binary package and configure environment variables
tar zxvf mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz mv mysql-5.7.30-linux-glibc2.12-x86_64/* /opt/mysql/vim /etc/profile
export PATH=/opt/mysql/bin:$PATH
source /etc/profile
3. Database Initialization
mysqld --initialize-insecure --user=mysql --basedir=/opt/mysql --datadir=/data/mysql/data Parameters: --initialize-insecure Do not use mysql Password policy, no temporary password --user Specify User --basedir Specify Software Path --datadir Specify Data Path Initialization occurs in the specified data directory mysql System Dependent Files
4. Profile
vim /etc/my.cnf # mysql automatically loads the configuration files in that directory
[mysqld] basedir=/opt/mysql datadir=/data/mysql/data server_id=10 user=mysql log_bin=/data/mysql/binlog/mysql-bin # mysql-bin is the specified binary file prefix binlog_format=row # Configure binlog to record in RBR mode socket=/tmp/mysql/mysql.sock # sock files are generated in this directory at database startup and disappear when the database is closed[client]
socket=/tmp/mysql/mysql.sock
Which paths will MySQL go to when it starts?
# Can be used in mysqld The instructions are found in the help information for
[root@db01 ~]# mysqld --help --verbose |grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf notes: By default, MySQL At startup, these profiles are read in turn, and if there are duplicate options, the last file setting will prevail. However, if you joined at startup--defaults-file=xxxx All of the above files will not be read when.
5. Database Startup
1. Startup mode one
Use mysql Software Directory support-files/mysql.server Just start and stop, it's just a bit of a hassle
/opt/mysql/support-files/mysql.server start/stop
2. Startup mode 2
Use mysql Software catalog/bin/mysql_safe To start
Start: mysql_safe &
Stop it: mysqladmin -uroot -p shutdown
3. Startup mode three
Use mysql Software catalog/bin/mysqld To start
Start:/opt/mysql/bin/mysqld --defaults-file=/etc/my.cnf
This method outputs the startup log to the screen for scenarios where even the log cannot be written in case of database exceptions.
4. mysql Maintenance mode
mysql_safe --skip-grant-tables --skip-networking &
5. Use sys-v Method.
cp support-files/mysql.server /etc/init.d/mysqldd
start-up/Stop it: service mysqldd start/stop perhaps /etc/init.d/mysqldd start/stop
6. Use systemctl Method.
cat /etc/systemd/system/mysqld.service Create a mysqld.service file[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
LimitNOFILE = 10000
ExecStart=/opt/mysql/bin/mysqld --defaults-file=/etc/my.cnf # Modify here, everything else is fixedConfiguration is complete and ready to use: systemctl start/stop mysqld
5.1 Startup modes comparison
1. The sys-v mode only implements simple start, stop, restart operations and cannot add parameters on the command line
The calling process for this method: This script calls bin/mysql_in the software directory Safe, then bin/mysqld is called to complete.
2. The systemctl method can only perform simple start-stop operations.
The calling process for this method: Start directly using the bin/mysqld+ specified configuration file in the software directory.
3. Start and stop of maintenance mode
Mysql_ Safe --skip-grant-tables --skip-networking & can be started using command line parameters to handle startup operations in special modes
Although the /etc/my.cnf configuration file will also be loaded, if the command line has the same parameters as the configuration file, the command line will prevail.
Supplement: 1, 2, 3 all load the contents of the / etc/my.cnf configuration file, but 3 is more flexible and can add required parameters to the command line to change the startup mode
5.2 Configure Remote Login after Startup
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' flush privileges;
6. Initialize using passwordless initialization
Available after database startup mysql Provided mysqladmin To set or modify an administrator password ( root) /opt/mysql/bin/mysqladmin -uroot -p password Password Of course, with the addition of environment variables, you can use them directly: mysqladmin -uroot -p password Password
mysqladmin The command sets the password the same as the password modification command, except that the old data password is needed the first time the password is changed.
7.Did you forget your administrator password?
Skip the authorization form, disable remote mode start, use the original grant or alter The command to change the password is definitely not possible, because the mode we started says simply that the authorization table will not be loaded and that the authorization table will certainly not be operational. This can be used flush privileges；To reload the authorization form and use it again grant or alter Command to set the password.
The authorization form is mysql In Library user surface
8. Configure mysql multiple instances
8.1 Prepare multiple directories
mkdir -p /data/330{7,8,9}/{data，binlog}
8.2 Preparing configuration files
cat > /data/3307/my.cnf <<EOF [mysqld] basedir=/opt/mysql/ datadir=/data/3307/data/ socket=/data/3307/mysql.sock log_error=/data/3307/mysql.log port=3307 server_id=7 log_bin=/data/3307/binlog/mysql-bin binlog_format=row EOF cat > /data/3308/my.cnf <<EOF [mysqld] basedir=/opt/mysql/ datadir=/data/3308/data/ socket=/data/3308/mysql.sock log_error=/data/3308/mysql.log port=3308 server_id=8 log_bin=/data/3308/binlog/mysql-bin binlog_format=row EOF cat > /data/3309/my.cnf <<EOF [mysqld] basedir=/opt/mysql/ datadir=/data/3309/data/ socket=/data/3309/mysql.sock log_error=/data/3309/mysql.log port=3309 server_id=9 log_bin=/data/3309/binlog/mysql-bin binlog_format=row EOF
8.3 Initialize three sets of data
cp /etc/my.cnf /etc/my.cnf.bak mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/opt/mysql mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/opt/mysql mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/opt/mysql
8.4 systemd Management Multiple Instances
cd /etc/systemd/system cp mysqld.service mysqld3307.service cp mysqld.service mysqld3308.service cp mysqld.service mysqld3309.service vim mysqld3307.service ExecStart=/opt/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf vim mysqld3308.service ExecStart=/opt/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf vim mysqld3309.service ExecStart=/opt/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
8.5 Authorize and Start Services
chown -R mysql.mysql /data/* systemctl start mysqld3307.service systemctl start mysqld3308.service systemctl start mysqld3309.service
8.6 Verify Multiple Instances
netstat -lnp|grep 330 mysql -S /data/3307/mysql.sock -e "select @@server_id" mysql -S /data/3308/mysql.sock -e "select @@server_id" mysql -S /data/3309/mysql.sock -e "select @@server_id"
8.7 Summary of Multiple Instances:
1. The principle of multiple instances:
Define multiple data directories and configuration files, initially using the same software, but different data directories will generate mysql-dependent system files in different directories you specify.
When configuring to use systemctl to manage instances, the essence is to specify different my.cnf profiles for the software directory/bin/mysqld. This allows different profiles to be loaded when starting different instances.
2. Connections with multiple instances:
There are two ways to connect mysql server:
1. tcp/ip connection, that is: Mysql-uroot-p-h127.0.0.1-P3306
2. Local socket connection, i.e. path to mysql-S/socket file. (Can only be used locally)
This machine has started several instances, each of which has a different socket file address. You can use sockets to connect the corresponding instances. You can also connect to different instances by specifying a port.