找一家秦皇岛市做网站的公司,禁止显示网站目录,电商网站是获取流量,石家庄招聘网最新招聘现有需求#xff0c;本地数据库迁移到阿里云上。
库名xy102表 test01test02test01 test023条数据。1、登录阿里云界面创建免费试用ECS实列。
阿里云登录页 (aliyun.com)](https://account.aliyun.com/login/login.htm?oauth_callbackhttps%3A%2F%2Fusercenter2.aliyun.com%…现有需求本地数据库迁移到阿里云上。
库名xy102表 test01test02test01 test023条数据。1、登录阿里云界面创建免费试用ECS实列。
阿里云登录页 (aliyun.com)](https://account.aliyun.com/login/login.htm?oauth_callbackhttps%3A%2F%2Fusercenter2.aliyun.com%2Fhome%2Fmyfreetier)2、选择2核4Gcentos7.6两台机器然后设置账户密码以及查看公网ip。
3、xshell客户端新建连接ip地址为ECS实例的公网地址。
4、
[rootmysql1 opt]# systemctl stop firewalld
[rootmysql1 opt]# setenforce 0[rootmysql1 opt]# systemctl restart mysqld[rootaliyun1 ~]# systemctl stop firewalld
[rootaliyun1 ~]# setenforce 05、通过scp用mysql进行传输数据压缩包
[rootmysql1 opt]# scp mysql-8.0.30-el7-x86_64.tar.gz root8.149.247.173:/opt/
The authenticity of host 8.149.247.173 (8.149.247.173) cant be established.
ECDSA key fingerprint is SHA256:sJmF545LYGbyN6MrAoU/CpP9Wa0wrFxcnDlQ4h5RJvo.
ECDSA key fingerprint is MD5:c8:7d:d7:dd:54:4e:e0:a4:1b:7a:0b:f0:eb:7e:fa:c0.
Are you sure you want to continue connecting (yes/no)? yesroot8.149.247.173s password:
mysql-8.0.30-el7-x86_64.tar.gz 100% 470MB 4.1MB/s 01:54 6、创建数据库xy102以及test1、test2表
create database xy102;CREATE TABLE test01(
id int(3),
name char(5),
address varchar(10)
);INSERT into test01 values(1,ly1,nanjing);
INSERT into test01 values(2,ly2,nanjing);
INSERT into test01 values(3,ly3,nanjing);CREATE TABLE test02(
id int(3),
name char(5),
address varchar(10)
);
INSERT into test02 values(1,ly1,chuzhou);
INSERT into test02 values(2,ly2,chuzhou);
INSERT into test02 values(3,ly3,chuzhou);7、备份数据库xy102.sql到/opt目录下
mysqldump -u root -p --databases xy102 /opt/xy102.sql
8、阿里云主机安装mysql数据库
[rootaliyun1 /]# cd /opt/
[rootaliyun1 opt]# ls
mysql-8.0.30-el7-x86_64.tar.gztar -xf mysql-8.0.30-el7-x86_64.tar.gz mv mysql-8.0.30-el7-x86_64 mysqlmv mysql /usr/local/#创建程序用户管理
useradd -s /sbin/nologin mysql#修改mysql目录和配置文件的权限chown -R mysql:mysql /usr/local/mysql/
[rootmysql1 mysql]# chown mysql:mysql /etc/my.cnf#修改配置文件vim /etc/my.cnf
[client]
port 3306
socket/usr/local/mysql/mysql.sock[mysqld]
user mysql
basedir/usr/local/mysql
datadir/usr/local/mysql/data
port 3306
character-set-serverutf8
pid-file /usr/local/mysql/mysqld.pid
socket/usr/local/mysql/mysql.sock
bind-address 0.0.0.0
skip-name-resolve
max_connections2048
default-storage-engineINNODB
max_allowed_packet16M
server-id 1
sql_mode STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION#设置环境变量申明/宣告mysql命令便于系统识别echo PATH$PATH:/usr/local/mysql/bin /etc/profilesource /etc/profile#初始化数据库cd /usr/local/mysql/bin/
cd /usr/local/mysql/bin/
./mysqld \
--initialize-insecure \
--usermysql \
--basedir/usr/local/mysql \
--datadir/usr/local/mysql/data报错./mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory解决办法原因分析服务器上面没有安装libiao这个包需要我们去下载一下就好了yum -y install libaio./mysqld --initialize-insecure --usermysql --basedir/usr/local/mysql --datadir/usr/local/mysql/data#设置系统识别进行操作
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chmod x /etc/init.d/mysqldsystemctl daemon-reload
ystemctl restart mysqld#初始化数据库密码
mysqladmin -u root -p password 123456
直接回车即可#进入数据库
mysql -u root -p123456
#创建用户并设置密码
mysql CREATE USER root% IDENTIFIED BY 123456;
Query OK, 0 rows affected (0.01 sec)#赋予远程连接的权限
mysql GRANT ALL PRIVILEGES ON *.* TO root%;
Query OK, 0 rows affected (0.00 sec)#刷新生效
mysql flush privileges;
Query OK, 0 rows affected (0.01 sec)#修改加密方式可以进行远程连接
ALTER USER root% IDENTIFIED WITH mysql_native_password BY 123456;9、传送xy102数据库到阿里云主机上
scp xy102.sql root8.149.247.173:/opt/
root8.149.247.173s password:
xy102.sql 100% 2769 97.1KB/s 00:00 10、热备份恢复数据库到阿里云主机上
cd /opt/
[rootaliyun1 opt]# ls
mysql-8.0.30-el7-x86_64.tar.gz xy102.sql
[rootaliyun1 opt]# mysql -u root -p /opt/xy102.sql
Enter password:
[rootaliyun1 opt]# mysql -u root -p123456
use xy102;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql show tables;
-----------------
| Tables_in_xy102 |
-----------------
| test01 |
| test02 |
-----------------
2 rows in set (0.01 sec)mysql select *from test01;
---------------------
| id | name | address |
---------------------
| 1 | ly1 | nanjing |
| 2 | ly2 | nanjing |
| 3 | ly3 | nanjing |
---------------------
3 rows in set (0.00 sec)mysql select * from test02;
---------------------
| id | name | address |
---------------------
| 1 | ly1 | chuzhou |
| 2 | ly2 | chuzhou |
| 3 | ly3 | chuzhou |
---------------------
3 rows in set (0.00 sec)解决navicat连接问题
