openEuler学习——mysql（第一次总结）

1、openEuler 二进制方式安装MySQL 8.0.x。

思路是先从官网获取安装包链接如下https://mirrors.aliyun.com/mysql/MySQL-8.0/mysql-8.0.28-linux-glibc2.12-x86_64.tar.xz

然后解压安装修改权限，可以参考mysql官方网站步骤

[root@openEuler-node1 ~]# wget -c https://mirrors.aliyun.com/mysql/MySQL-8.0/mysql-8.0.28-linux-glibc2.12-x86_64.tar.xz
[root@openEuler-node1 ~]# dnf install tar xz -y
[root@openEuler-node1 ~]# groupadd -g 27 -r mysql
[root@openEuler-node1 ~]# useradd -u 27 -g 27 -c 'MySQL Server' -r -s /sbin/nologin mysql
[root@openEuler-node1 ~]# tar xf mysql-8.0.28-linux-glibc2.12-x86_64.tar.xz -C /usr/local/
[root@openEuler-node1 ~]# ln -sv /usr/local/mysql-8.0.28-linux-glibc2.12-x86_64/ /usr/local/mysql
'/usr/local/mysql' -> '/usr/local/mysql-8.0.28-linux-glibc2.12-x86_64/'
[root@openEuler-node1 ~]# cd /usr/local/mysql
[root@openEuler-node1 mysql]# mkdir mysql-files
[root@openEuler-node1 mysql]# chown mysql:mysql mysql-files
[root@openEuler-node1 mysql]#  chmod 750 mysql-files
[root@openEuler-node1 mysql]# bin/mysqld --initialize --user=mysql
[root@openEuler-node1 mysql]# bin/mysql_ssl_rsa_setup
[root@openEuler-node1 mysql]# bin/mysqld_safe --user=mysql
[root@openEuler-node1 mysql]# bin/mysqld --initialize --user=mysql（会生成初始密码）
[root@openEuler-node1 mysql]# vi /etc/profile.d/mysql.sh
export PATH=$PATH:/usr/local/mysql/bin/
[root@openEuler-node1 mysql]# source /etc/profile.d/mysql.sh 
[root@openEuler-node1 mysql]# yum install ncurses-compat-libs
[root@openEuler-node1 mysql]# mysql -uroot -p'o<_U>gce?391'（密码是上几条命令随机出来的）
mysql> alter user root@localhost identified by 'Root';
[root@openEuler-node1 mysql]# cp support-files/mysql.server  /etc/init.d/mysqld
[root@openEuler-node1 mysql]# chkconfig --add mysqld


[root@openEuler-node2 etc]# scp /etc/my.cnf 192.168.20.146:/etc
[root@openEuler-node2 etc]# scp -r /etc/my.cnf.d 192.168.20.146:/etc/

# vi /etc/my.cnf.d//mysql-server.cnf 

basedir=/usr/local/mysql/
datadir=/usr/local/mysql/data/
socket=/tmp/mysql.sock

log-error=/usr/local/mysql/data/mysqld.log
pid-file=/usr/local/mysql/data/mysqld.pid

2、备份数据库

mysql> create database school;
Query OK, 1 row affected (0.01 sec)

mysql> use school
Database changed
mysql> CREATE TABLE `Student` (
    ->   `Sno` int(10) NOT NULL COMMENT '学号',  `Sname` varchar(16) NOT NULL COMMENT '姓名',
    ->   `Ssex` char(2) NOT NULL COMMENT '性别',  `Sage` tinyint(2) NOT NULL DEFAULT '0' COMMENT '学生年龄',
    ->   `Sdept` varchar(16) DEFAULT 'NULL' COMMENT '学生所在系别',  PRIMARY KEY (`Sno`)
    -> ) ;
Query OK, 0 rows affected, 2 warnings (0.09 sec)

mysql> INSERT INTO `Student` VALUES (1, '陆亚', '男', 24, '计算机网络'),(2, 'tom', '男', 26, '英语'),(3, '张阳', '男', 21, '物流管理'), (4, 'alex', '女', 22, '电子商务');
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

[root@openEuler-node1 mysqlbak]# mysqldump --opt -B school > school.sql 

完全备份数据库 

3.备份数据库school到/backup目录

 

[root@openEuler-node1 /]# mkdir /backup
[root@openEuler-node1 /]# cd /backup/
[root@openEuler-node1 backup]# mysqldump --opt -B school > school.sql

4.备份MySQL数据库为带删除表的格式，能够让该备份覆盖已有数据库而不需要手动删除原有数据库

[root@openEuler-node1 backup]# mysqldump --add-drop-table -uroot -pRoot school > school2.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@openEuler-node1 backup]# ll
总用量 8
-rw-r--r--. 1 root root 2226  2月 19 22:32 school2.sql
-rw-r--r--. 1 root root 2226  2月 19 22:32 school.sql

5.直接将MySQL数据库压缩备份

[root@openEuler-node1 backup]# mysqldump  -uroot -pRoot school | gzip > backupfile.sql.gz
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@openEuler-node1 backup]# ll
总用量 12
-rw-r--r--. 1 root root  939  2月 19 22:34 backupfile.sql.gz
-rw-r--r--. 1 root root 2226  2月 19 22:32 school2.sql
-rw-r--r--. 1 root root 2226  2月 19 22:32 school.sql