CentOS7安装MySQL和MariaDB + 使用SQLYog进行数据库之间的数据传输_centos7.0使用sq连接数据库

一、选择MySQL5.7 -> Linux - Generic -> 64位 -> Compressed TAR Archive进行下载

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz

二、用tar -zxvf进行解压

三、添加系统mysql组和mysql用户

添加系统mysql组 groupadd mysql

添加mysql用户 useradd -r -g mysql mysql （添加完成后可用id mysql查看）

四、安装数据库（路径视自己情况而定）
cd /usr/local/mysql/mysql-5.7.26-linux-glibc2.12-x86_64
chown -R mysql:mysql ./
bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/mysql-5.7.26-linux-glibc2.12-x86_64 --datadir=/usr/local/mysql/mysql-5.7.26-linux-glibc2.12-x86_64/data（这里完成后会生成临时密码）

出现错误：bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
解决：yum install -y libaio

修改当前目录拥有者为mysql用户 chown -R mysql:mysql ./
修改当前data目录拥有者为mysql用户 chown -R mysql:mysql data
vim /etc/init.d/mysqld ，在第46行修改路径

五、启动mysql
service mysqld start
报错：Failed to start mysql.service: Unit not found.
参考：https://www.cnblogs.com/progor/archive/2018/01/30/8387301.html
解决：yum install -y mariadb-server
systemctl start mariadb.service
systemctl enable mariadb.service

mysql -uroot -p 临时生成的密码
报错：Access denied for user ‘root’@‘localhost’ (using password: YES)
参考：https://blog.csdn.net/u012186420/article/details/82254617 可顺利解决问题

登录MySQL后，
use mysql;
update user set password=password(“你设置的密码”)where user = “root”;
flush privileges;

附：
我在第四步时曾经vim /etc/my.cnf:

[mysqld]
character_set_server=utf8
init_connect='SET NAMES utf8'
basedir=/usr/local/mysql/mysql-5.7.26-linux-glibc2.12-x86_64
datadir=/usr/local/mysql/mysql-5.7.26-linux-glibc2.12-x86_64/data
socket=/usr/local/mysql/mysql-5.7.26-linux-glibc2.12-x86_64/tmp/mysql.sock
#不区分大小写 
lower_case_table_names = 1
#不开启sql严格模式
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
log-error=/usr/local/mysql/mysql-5.7.26-linux-glibc2.12-x86_64/log/mysqld.log
pid-file=/usr/local/mysql/mysql-5.7.26-linux-glibc2.12-x86_64/data/mysqld.pid

1
2
3
4
5
6
7
8
9
10
11
12
13

但是如此一来，我在systemctl start mariadb.service时会报错：
job for mariadb.service failed because the control process exited with error code .
去/var/log/mariadb/mariadb.log查看报错信息：

190701 08:12:25 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/mysql-5.7.26-linux-glibc2.12-x86_64/data
190701  8:12:25 [Note] /usr/libexec/mysqld (mysqld 5.5.60-MariaDB) starting as process 8762 ...
190701  8:12:25 InnoDB: The InnoDB memory heap is disabled
190701  8:12:25 InnoDB: Mutexes and rw_locks use GCC atomic builtins
190701  8:12:25 InnoDB: Compressed tables use zlib 1.2.7
190701  8:12:25 InnoDB: Using Linux native AIO
190701  8:12:25 InnoDB: Initializing buffer pool, size = 128.0M
190701  8:12:25 InnoDB: Completed initialization of buffer pool
InnoDB: Error: checksum mismatch in data file ./ibdata1
190701  8:12:25 InnoDB: Could not open or create data files.
190701  8:12:25 InnoDB: If you tried to add new data files, and it failed here,
190701  8:12:25 InnoDB: you should now edit innodb_data_file_path in my.cnf back
190701  8:12:25 InnoDB: to what it was, and remove the new ibdata files InnoDB created
190701  8:12:25 InnoDB: in this failed attempt. InnoDB only wrote those files full of
190701  8:12:25 InnoDB: zeros, but did not yet use them in any way. But be careful: do not
190701  8:12:25 InnoDB: remove old data files which contain your precious data!
190701  8:12:25 [ERROR] Plugin 'InnoDB' init function returned error.
190701  8:12:25 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
190701  8:12:25 [Note] Plugin 'FEEDBACK' is disabled.
190701  8:12:25 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
190701  8:12:25 [ERROR] Unknown/unsupported storage engine: InnoDB
190701  8:12:25 [ERROR] Aborting
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

我尝试配置了innodb_data_home_dir和innodb_data_file_path，但是似乎没有起到作用。

所以最终我的/etc/my.cnf是这样的：（那个skip-grant-tables我选择加在mysqld下面，试过加在最后一行，但是依旧不行）

[mysqld]
character_set_server=utf8
init_connect='SET NAMES utf8'
#basedir=/usr/local/mysql/mysql-5.7.26-linux-glibc2.12-x86_64
#datadir=/usr/local/mysql/mysql-5.7.26-linux-glibc2.12-x86_64/data
#socket=/usr/local/mysql/mysql-5.7.26-linux-glibc2.12-x86_64/tmp/mysql.sock
#不区分大小写 
lower_case_table_names = 1
#不开启sql严格模式
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
#log-error=/usr/local/mysql/mysql-5.7.26-linux-glibc2.12-x86_64/log/mysqld.log
#pid-file=/usr/local/mysql/mysql-5.7.26-linux-glibc2.12-x86_64/data/mysqld.pid
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#skip-grant-tables
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
#skip-grant-tables

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

六、在SQLYog里连接CentOS7里的数据库：
参考：https://blog.csdn.net/axing2015/article/details/81107606
新建连接->主机地址填IP地址->用户名填root->密码填自己设置的密码
（我使用了阿里云服务器，所以我在安全组那里开放了3306端口）

点击连接时报错：Host ‘xxx’ is not allowed to connect to this MariaDB server
参考：https://www.cnblogs.com/vathena/p/4012399.html
解决：
grant all privileges on . to ‘root’@’%’ identified by ‘你设置的密码’ with grant option;
flush privileges;

七、在不同数据库之间进行数据传输：
选择自己想要传输数据的数据库源，右键，选择“将数据库复制到不同的主机/数据库”
出现如下窗口：

按自己情况操作即可