迁移
步骤中采用的是上传
mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
也可以wget下载
1、安装数据库服务器db01环境
[root@db01 /server/tools]# #1.创建用户
[root@db01 /server/tools]# useradd mysql -s /sbin/nologin -M
ibc2.12-x86_64 /application/mysql-5.7.26
ln -s /application/mysql-5.7.26/ /application/mysql
#4、配置配置文件
rpm -e --nodeps mariadb-libs
cat >/etc/my.cnf<<EOF
[mysqld]
basedir = /application/mysql/
datadir = /application/mysql/data
socket = /tmp/mysql.sock
server_id = 1
port = 3306
log_error = /application/mysql/data/oldboy_mysql.err
[mysql]
socket = /tmp/mysql.sock
prompt = oldboy [\\d]>
EOF
#5.初始化数据库
rpm -qa mariadb-libs
yum install libaio-devel -y
mkdir -p /application/mysql/data
chown -R mysql.mysql /application/mysql/
/application/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/application/mysql/ --datadir=/application/mysql/data
#6、配置启动
cat >/etc/systemd/system/mysqld.service<<EOF
[Unit]
Description=MySQL Server by oldboy
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
ExecStart=/application/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
EOF
systemctl start mysqld
systemctl enable mysqld
netstat -lntup|grep mysql
#7.登录测试
echo 'export PATH=/application/mysql/bin:$PATH' >>/etc/profile
. /etc/profile
mysql
quit[root@db01 /server/tools]# id mysql
uid=1001(mysql) gid=1001(mysql) 组=1001(mysql)
[root@db01 /server/tools]# #2.创建目录上传软件
[root@db01 /server/tools]# mkdir -p /server/tools/
[root@db01 /server/tools]# cd /server/tools/
[root@db01 /server/tools]# #wget -----
[root@db01 /server/tools]#
[root@db01 /server/tools]# #3、解压安装
[root@db01 /server/tools]# tar xf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
[root@db01 /server/tools]# mkdir -p /application
[root@db01 /server/tools]# mv mysql-5.7.26-linux-glibc2.12-x86_64 /application/mysql-5.7.26
[root@db01 /server/tools]# ln -s /application/mysql-5.7.26/ /application/mysql
[root@db01 /server/tools]#
[root@db01 /server/tools]# #4、配置配置文件
[root@db01 /server/tools]# rpm -e --nodeps mariadb-libs
[root@db01 /server/tools]# cat >/etc/my.cnf<<EOF
> [mysqld]
> basedir = /application/mysql/
> datadir = /application/mysql/data
> socket = /tmp/mysql.sock
> server_id = 1
> port = 3306
> log_error = /application/mysql/data/oldboy_mysql.err
>
> [mysql]
> socket = /tmp/mysql.sock
> prompt = oldboy [\\d]>
> EOF
[root@db01 /server/tools]#
[root@db01 /server/tools]# #5.初始化数据库
[root@db01 /server/tools]# rpm -qa mariadb-libs
[root@db01 /server/tools]# yum install libaio-devel -y
[root@db01 /server/tools]#
[root@db01 /server/tools]# mkdir -p /application/mysql/data
[root@db01 /server/tools]# chown -R mysql.mysql /application/mysql/
[root@db01 /server/tools]#
[root@db01 /server/tools]# /application/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/application/mysql/ --datadir=/application/mysql/data
[root@db01 /server/tools]#
[root@db01 /server/tools]# #6、配置启动
[root@db01 /server/tools]# cat >/etc/systemd/system/mysqld.service<<EOF
> [Unit]
> Description=MySQL Server by oldboy
> 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
> ExecStart=/application/mysql/bin/mysqld --defaults-file=/etc/my.cnf
> LimitNOFILE = 5000
> EOF
[root@db01 /server/tools]# systemctl start mysqld
[root@db01 /server/tools]# systemctl enable mysqld
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /etc/systemd/system/mysqld.service.
[root@db01 /server/tools]# netstat -lntup|grep mysql
tcp6 0 0 :::3306 :::* LISTEN 8049/mysqld
[root@db01 /server/tools]#
[root@db01 /server/tools]# #7.登录测试
[root@db01 /server/tools]# echo 'export PATH=/application/mysql/bin:$PATH' >>/etc/profile
[root@db01 /server/tools]# . /etc/profile
[root@db01 /server/tools]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
oldboy [(none)]>quit
Bye
[root@db01 /server/tools]#
2、迁移
web01:
[root@web01 /application/nginx/html/blog]# mysqldump -uroot -poldboy123 -A -B|gzip >/tmp/web02_db.sql.gz
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@web01 /application/nginx/html/blog]# scp /tmp/web02_db.sql.gz 10.0.0.51:/tmp
The authenticity of host '10.0.0.51 (10.0.0.51)' can't be established.
ECDSA key fingerprint is SHA256:V6An/JuDpwWUEo+oe9g3SyUWlWTckb4HaZ+MXAaeHok.
ECDSA key fingerprint is MD5:7f:30:d0:61:e6:d7:37:ab:a1:3c:02:55:e2:ef:db:4f.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.0.0.51' (ECDSA) to the list of known hosts.
root@10.0.0.51's password:
web02_db.sql.gz 100% 313KB 12.1MB/s 00:00
[root@web01 ~]# cd /application/nginx/html/blog/
[root@web01 /application/nginx/html/blog]# vim wp-config.php
define( 'DB_NAME', 'wordpress' );
/** MySQL database username */
define( 'DB_USER', 'wordpress' );
/** MySQL database password */
define( 'DB_PASSWORD', 'oldboy123' );
/** MySQL hostname */
define( 'DB_HOST', '172.16.1.51' );
[root@web01 /application/nginx/html/blog]# systemctl stop mysqld
[root@web01 /application/nginx/html/blog]# systemctl disable mysqld
Removed symlink /etc/systemd/system/multi-user.target.wants/mysqld.service.
[root@web01 /application/nginx/html/blog]# lsof -i :3306
db01:
[root@db01 /tmp]# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 8049 mysql 20u IPv6 43138 0t0 TCP *:mysql (LISTEN)
[root@db01 /tmp]# cd /tmp
[root@db01 /tmp]# gzip -d web02_db.sql.gz
[root@db01 /tmp]# mysql </tmp/web02_db.sql
[root@db01 /tmp]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
oldboy [(none)]>
oldboy [(none)]>grant all privileges on wordpress.* to wordpress@'172.16.1.%' identified by 'oldboy123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
oldboy [(none)]>flush privileges;
Query OK, 0 rows affected (0.01 sec)
oldboy [(none)]>select user,authentication_string,host from mysql.user;
+---------------+-------------------------------------------+------------+
| user | authentication_string | host |
+---------------+-------------------------------------------+------------+
| root | *FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515 | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| wordpress | *FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515 | 172.16.1.% |
+---------------+-------------------------------------------+------------+
4 rows in set (0.00 sec)
oldboy [(none)]>^DBye
[root@db01 /tmp]#
