Shulou(Shulou.com)06/01 Report--

Task background

As the existing business architecture can no longer meet the current business requirements, under the premise of ensuring the integrity of the data, it is necessary to migrate the original database to another separate server, and upgrade the original mysql database version 5.6.31 to 5.6.35 in the original LAMP environment under the condition of normal service.

Task requirements

1. Make a full backup of the database during the business maintenance time (02:00-4:00)

two。 Migrate the MySQL database from the old server to the new server and upgrade version 5.6.35

Task disassembly

1. Install Centos7.5 on the new server and install the MySQL-5.6.35 version of the database

two。 Stop monitoring, stop front-end applications, stop MySQL database

3. Back up the database (omitted)

4. Migrate database-> synchronize database files to a new environment (rsync/scp)

5. Test verification

Task solution

Environment description

Concrete steps

1. The old environment stops the corresponding service.

1) stop monitoring strategy 2) stop the application service apache and database [root@lamp ~] # service httpd stop [root@lamp ~] # service mysql stopShutting down MySQL.. SUCCESS!

two。 Deploy and install MySQL in the new environment

Download mysql software from ① official website

# du-sh mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz301M mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz

② installation requirements

Installation directory (basedir): / usr/local/mysql database directory (datadir): / usr/local/mysql/data port: 3306socket file: / tmp/mysql.sock error log file: / usr/local/mysql/data/mysql.err

③ glibc installation steps

1) decompress the package

# tar-xf mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz# cd / usr/local/# ln-s / soft/installer/mysql-5.6.35-linux-glibc2.5-x86_64 mysql Note: enter the soft link in the / usr/local directory

2) create users and modify directory permissions

1. Check to see if the user has [root@db01 mysql] # id mysqlid: mysql: no such user [root@db01 mysql] # useradd-r-s / sbin/nologin mysql [root@db01 mysql] # id mysqluid=997 (mysql) gid=995 (mysql) group = 995 (mysql) 2. Change the permissions of the mysql installation directory [root@db01 mysql] # chown-R mysql.mysql / usr/local/mysql/ [root@db01 mysql] # ll-d / usr/local/mysql/drwxr-xr-x 13 mysql mysql 191 October 9 10:26 / usr/local/mysql/

3) modify the configuration file

Modify / etc/my.cnf file [root@db01 mysql] # vim / etc/ my.cnf [mysqld] basedir=/usr/local/mysqldatadir=/usr/local/mysql/dataport=3307socket=/usr/local/mysql/mysql.socklog-error=/usr/local/mysql/data/mysql3307.err

4) copy the startup script

[root@db01 mysql] # pwd/usr/local/mysql [root@db01 mysql] # cp support-files/mysql.server / etc/init.d/mysql

5) initialization description

1. If you install a new database normally and use it directly, you need to initialize the database and then start using it.

two。 The current requirement is to migrate the original MySQL database files to the data directory of the new database, so there is no need for initialization

3. Migrate the database to the new environment

Description: operation on the new database server

① synchronizes the old library data files to the new library data directory

[root@db01 mysql] # rsync-av 10.1.1.1:/usr/local/mysql/data/ / usr/local/mysql/data

② starts a new database

Start the new database directly and check the log file (to see if any options are incompatible) [root@db01 mysql] # service mysql start

③ upgrade database (focus, upgrade database files)

Use the mysql_upgrade command to check the compatibility of the data file [root@db01] # / usr/local/mysql/bin/mysql_upgrade-S / tmp/mysql.sock-p123 Note: 1. In the actual environment, it is recommended to use the-s parameter, do not check the business table, only check the system file 2. To upgrade, you need to connect to the database.-p password-S specifies the socket file [root@db01 mysql] # / usr/local/mysql/bin/mysql_upgrade-pEnter password:Looking for 'mysql' as: / usr/local/mysql/bin/mysqlLooking for' mysqlcheck' as: / usr/local/mysql/bin/mysqlcheckError: Failed while fetching Server version! Could be due to unauthorized access.FATAL ERROR: Upgrade failed reason: failed to connect to the database

4. The original web service connects to the new database (LAMP= > LNMP)

① modifies the configuration file for the php connection mysql (if any)

[root@lamp ~] # find /-name php.ini/usr/local/lib/php.ini [root@lamp ~] # vim / usr/local/lib/ php.ini [MySQL] mysql.default_port = 3306mysql.default_host = 10.1.1.37 [MySQLi] mysql.default_port = 3306mysql.default_host = 10.1.1.37

② modifies site profile to connect to database

Modify the configuration file [root@lamp www] # pwd/var/html/www [root@lamp www] # vim wp-config.php...// * * MySQL settings of the database directory where the www.myblog.net website resides-specific information comes from the name of the host * * / / * * WordPress database * / define ('DB_NAME',' myblog') that you are using / * * MySQL database user name * / define ('DB_USER',' root'); / * * MySQL database password * / define ('DB_PASSWORD',' 123'); / * * MySQL host * / define ('DB_HOST',' 10.1.1.37'); / * default text encoding when creating data tables * / define ('DB_CHARSET',' utf8')

③ new database authorization for web service

The new mysql database needs to authorize root@10.1.1.19 users mysql > grant all on *. * to 'root'@'10.1.1.19' identified by' 123 users to query OK, 0 rows affected (0.00 sec) mysql > flush privileges;Query OK, 0 rows affected (0.00 sec) Note: turn off all firewall firewalld and selinux, otherwise you won't be able to connect!

④ starts the web service

[root@lamp ~] # service apache start [root@lamp ~] # netstat-nltp | grep httpdtcp 0 0: 80:: * LISTEN 1376/httpd

⑤ test verification

1. Browser access 2. Remote access to [root@lamp] # mysql-uroot-h20.1.1.37-uroot-p123 using the mysql tool on the web server

Learning resources:

Play MySQL in 5 days.

Https://pan.baidu.com/s/1Cb1eGwb4dn45I4tbQ2Ggzg extraction code: yfw6

Https://pan.baidu.com/s/1qXYYhcS extraction code: mkqu

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.