直接执行下面命令然后输入密码也可以备份 mysqldump -u root -h 127.0.0.1 -p --all-databases > /backup/allBackup.sql Enter your password
脚本里执行报warning, 因为命令行里直接使用了密码, MySQL不推荐这样做. mysqldump -u root -h 127.0.0.1 -p$PASSWD--all-databases > /backup/allBackup.sql mysqldump: [Warning] Using a password on the command line interface can be insecure.
# CentOS-Base.repo # # The mirror system uses the connecting IP address of the client and the # update status of each mirror to pick mirrors that are updated to and # geographically close to the client. You should use this for CentOS updates # unless you are manually picking other mirrors. # # If the mirrorlist= does not work for you, as a fall back you can try the # remarked out baseurl= line instead. # #
[base] name=CentOS-$releasever - Base #mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os&infra=$infra baseurl=https://mirrors.ustc.edu.cn/centos/$releasever/os/$basearch/ gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7
#additional packages that may be useful [extras] name=CentOS-$releasever - Extras #mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=extras&infra=$infra baseurl=https://mirrors.ustc.edu.cn/centos/$releasever/extras/$basearch/ gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7
#additional packages that extend functionality of existing packages [centosplus] name=CentOS-$releasever - Plus #mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=centosplus&infra=$infra baseurl=https://mirrors.ustc.edu.cn/centos/$releasever/centosplus/$basearch/ gpgcheck=1 enabled=0 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7
vim /etc/fstab UUID=a5a8e148-8c6e-49c0-be74-dc1e61fc170f /data ext4 defaults 0 0
要挂载的分区设备号 挂载点 文件系统类型 挂载选项 是否备份 是否检测
fdisk单次分区不能超过2T, 超过2T需要使用parted
[root@iz5bg05ape183r0ipx22lrz ~]# part parted partprobe partx [root@iz5bg05ape183r0ipx22lrz ~]# parted /dev/vdc GNU Parted 3.1 Using /dev/vdc Welcome to GNU Parted! Type 'help' to view a list of commands. (parted) p Model: Virtio Block Device (virtblk) Disk /dev/vdc: 3221GB Sector size (logical/physical): 512B/512B Partition Table: msdos Disk Flags:
Number Start End Size Type File system Flags
(parted) mklabel gpt Warning: The existing disk label on /dev/vdc will be destroyed and all data on this disk will be lost. Do you want to continue? Yes/No? yes (parted) mkpart Partition name? []? vdc1 File system type? [ext2]? ext4 Start? 0 End? 3221GB Warning: The resulting partition is not properly aligned for best performance. Ignore/Cancel? ignore (parted) p Model: Virtio Block Device (virtblk) Disk /dev/vdc: 3221GB Sector size (logical/physical): 512B/512B Partition Table: gpt Disk Flags:
Number Start End Size File system Name Flags 1 17.4kB 3221GB 3221GB vdc1
(parted) quit Information: You may need to update /etc/fstab.
限制mysql登录 update mysql.user set host='10.45.51.0/24' where user='root' and host='127.0.0.1'; grant all on *.* to 'root'@'10.40.' identified by 'yourpassword';
指定ip或者网段 指定 192.168.128.8 可以登录 update mysql.user set host='192.168.128.8' where user ='root' and host = 'localhost'; grant all on *.* to 'root'@'192.168.128.8' identified by 'yourpassword'; grant all on *.* to 'root'@'192.168.128.8' identified by 'yourpassword' with grant option; 如果 grant all 这种写法提示语法错误, 在语句后面加上 with grant option
指定 192.168.128.0/24网段 可以登录 update mysql.user set host='192.168.128.%' where user ='root' and host = 'localhost'; grant all on *.* to 'root'@'192.168.128.%' identified by 'yourpassword'; 也有下面这种写法, 但是经过测试无效(mysql community 5.7.37 具体没有深入) update mysql.user set host='192.168.128.' where user ='root' and host = 'localhost';
general_log 开启
mysql> show global variables like '%general%'; +------------------+----------------------------+ | Variable_name | Value | +------------------+----------------------------+ | general_log | OFF | | general_log_file | /var/lib/mysql/centos7.log | +------------------+----------------------------+ 2 rows inset (0.02 sec)
使用命令修改的设置会在服务重启之后失效
临时生效:
mysql>set global general_log_file='/tmp/general.lg'; # 设置路径 mysql>set global general_log=on; # 开启general log模式 mysql>set global general_log=off; # 关闭general log模式
create database cloud; create user ‘nextcloud‘@’localhost’ identified by ‘zhuangzhuang123…’; grant all on cloud.* to nextcloud@’localhost’ identified by ‘zhuangzhuang123…’; flush privileges;
$ sqlplus / as sysdba SQL> show parameter local_listener SQL> alter system set local_listener="(address = (protocol = tcp)(host = localhost)(port = 11521))"; SQL> show parameter local_listener
重启监听:lsnrctl reload
四、数据导入导出
4.1 创建逻辑目录
该命令不会在操作系统创建真正的目录(请先创建真正的目录),最好以system等管理员创建逻辑目录。
shell> sqlplus / as sysdba SQL> create directory data_dir as '/data/backup'; Directory created.
Import: Release 11.2.0.1.0 - Production on Wed Apr 12 22:01:24 2023 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ;;; Legacy Mode Active due to the following parameters: ;;; Legacy Mode Parameter: "ignore=TRUE" Location: Command Line, Replaced with: "table_exists_action=append" Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@172.19.3.12:11521/orcl.test directory=data_dir dumpfile=all.dmp logfile=import.log full=y table _exists_action=append Processing object type DATABASE_EXPORT/TABLESPACE ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists ORA-31684: Object type TABLESPACE:"TEMP" already exists ORA-31684: Object type TABLESPACE:"USERS" already exists ORA-31684: Object type TABLESPACE:"TRANSFERDB" already exists Processing object type DATABASE_EXPORT/PROFILE ORA-31684: Object type PROFILE:"MONITORING_PROFILE" already exists Processing object type DATABASE_EXPORT/SYS_USER/USER Processing object type DATABASE_EXPORT/SCHEMA/USER ORA-31684: Object type USER:"OUTLN" already exists . . 数据已脱敏, 中间省略 n 行 .
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTANCE Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJ Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA Processing object type DATABASE_EXPORT/AUDIT Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 7644 error(s) at 22:17:04