什么是DBA

发表于 更新于 分类于

一、什么是DBA

DBA，数据库管理员Data (数据) Base（介质） Administrator（管理员）

二、数据库的种类

RDBMS :关系型数据库 管理系统 Oracle 、MySQL、PG、MSSQL

NoSQL :非关系型的 MongoDB、Redis、ES

NewSQL :新型的分布式解决方案 TiDB 、Spanner 、AliSQL、OB、PolarDB

ps:阿里云花了8年去IOE（ibm oracle emc）在mysql的基础上开发了自己的AliSQL（RDS）

​ 淘宝的数据库架构

数据库的使用排行榜https://db-engines.com/en/ranking

数据库版本的选择5.6-5.7

三、MySQL的部署

这边下载的是5.7.26 通用的（二进制文件）包，直接就可以使用。源码虽然小，但是需要编译，时间比较长。

数据库的部署将数据和软件分开，位于不同的磁盘中，保障数据的安全性。

软件部署： 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#创建目录，将软件上传到此目录
mkdir /Server/Tool -p 
#解压软件包
tar -xf mysql-5.7.26-linux-glibc2.12-x86_64.tar
tar -xf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
#为软件目录做个软连接
mkdir /application
ln -s /Server/Tool/mysql-5.7.26-linux-glibc2.12-x86_64 /application/mysql
#卸载系统自带的数据库
rpm -qa | grep mariadb
         mariadb-libs-5.5.60-1.el7_5.x86_64
yum remove  mariadb-libs
#创建数据系统用户
useradd -s /sbin/nologin mysql
id mysql
		uid=1001(mysql) gid=1001(mysql) groups=1001(mysql)
#设置环境变量，编辑/etc/profile
export PATH=/application/mysql/bin:$PATH
#加载/etc/profile文件
source /etc/profile

#检查mysql版本
mysql -V
		mysql  Ver 14.14 Distrib 5.7.26, for linux-glibc2.12 (x86_64) using  EditLine wrapper

数据挂载：

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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
#添加一块磁盘20G的虚拟磁盘
Disk /dev/sdb: 21.5 GB, 21474836480 bytes, 41943040 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
#格式化磁盘，xfs格式
mkfs.xfs /dev/sdb
		meta-data=/dev/sdb               isize=512    agcount=4, agsize=1310720 blks
        		 =                       sectsz=512   attr=2, projid32bit=1
        		 =                       crc=1        finobt=0, sparse=0
		data     =                       bsize=4096   blocks=5242880, imaxpct=25
         		=                       sunit=0      swidth=0 blks
		naming   =version 2              bsize=4096   ascii-ci=0 ftype=1
		log      =internal log           bsize=4096   blocks=2560, version=2
         		=                       sectsz=512   sunit=0 blks, lazy-count=1
		realtime =none                   extsz=4096   blocks=0, rtextents=0
#创建/data目录，挂载磁盘到此目录
mkdir /data
blkid
		/dev/sr0: UUID="2018-11-25-23-54-16-00" LABEL="CentOS 7 x86_64" TYPE="iso9660" PTTYPE="dos" 
		/dev/sda1: UUID="34194035-3761-4a91-a545-7382755db54e" TYPE="xfs" 
		/dev/sda2: UUID="09dbb797-47b7-41c9-9d44-0ff706410659" TYPE="swap" 
		/dev/sda3: UUID="4cc606e9-2c8b-4edf-b966-8a22ad4ab241" TYPE="xfs" 
		/dev/sdb: UUID="5e764ebf-9b54-4f72-a607-f176e3235727" TYPE="xfs" 
mount -a /dev/sdb /data
#写入/etc/fstab文件中，实现开机自动挂载
UUID=5e764ebf-9b54-4f72-a607-f176e3235727 /data                   xfs     defaults        0 0
#授权
chown mysql:mysql /application/mysql -R
mkdir /data/mysql/data -p
chown mysql:mysql /data/ -R
#初始化数据
#5.6版本初始化  /applicaton/mysql/scripts/mysql_install_db
#5.7版本初始化命令,--initialize参数:对于密码复杂度进行定制，12位4种 密码过期时间180天
mysqld --initialize --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data
#出现报错，mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
#缺少依赖libraries-devel
#初始化完成，会生成一个临时密码，相对较麻烦
2021-11-13T09:50:06.545826Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-11-13T09:50:08.403120Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-11-13T09:50:08.985675Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-11-13T09:50:09.110939Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 167aca30-4467-11ec-9d99-000c291c5343.
2021-11-13T09:50:09.114991Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-11-13T09:50:09.121738Z 1 [Note] A temporary password is generated for root@localhost: DeFuNh,_c7=t
#这里用另一种初始化方式，不用密码，直接登录,因为已经初始化，需要先删除
rm /data/mysql/data -rf
mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data
#在数据库数据目录下会生成
ll /data/mysql/data
	-rw-r----- 1 mysql mysql       56 Nov 13 18:03 auto.cnf
	-rw-r----- 1 mysql mysql      419 Nov 13 18:03 ib_buffer_pool
	-rw-r----- 1 mysql mysql 12582912 Nov 13 18:03 ibdata1
	-rw-r----- 1 mysql mysql 50331648 Nov 13 18:03 ib_logfile0
	-rw-r----- 1 mysql mysql 50331648 Nov 13 18:03 ib_logfile1
	drwxr-x--- 2 mysql mysql     4096 Nov 13 18:03 mysql
	drwxr-x--- 2 mysql mysql     8192 Nov 13 18:03 performance_schema
	drwxr-x--- 2 mysql mysql     8192 Nov 13 18:03 sys
#写入mysqld的配置文件
cat > /etc/my.conf <<EOF
[mysqld]
user=mysql
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=6
port=3306
[mysql]
socket=/tmp/mysql.sock
EOF
#启动数据（两种方法）
#基于6版本系统以或5.6版本数据库的启动方式
cp support-files/mysql.server /etc/init.d/mysqld
service mysqld start 
/etc/init.d/mysqld stop
#基于7版本系统的systemctl管理方式
cat > /etc/systemd/system/mysqld.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en-using-systemd.html
After=network.tartget
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 stop mysqld

四、如何分析处理MySQL数据库无法启动

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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
#数据库数据
 ll /data/mysql/data
total 110644
-rw-r----- 1 mysql mysql       56 Nov 15 14:47 auto.cnf
-rw-r----- 1 mysql mysql      296 Nov 15 17:40 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Nov 15 17:40 ibdata1
-rw-r----- 1 mysql mysql 50331648 Nov 15 17:40 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Nov 15 14:47 ib_logfile1
-rw-r----- 1 mysql mysql    14958 Nov 15 17:31 localhost.localdomain.err
drwxr-x--- 2 mysql mysql     4096 Nov 15 14:47 mysql
drwxr-x--- 2 mysql mysql     8192 Nov 15 14:47 performance_schema
drwxr-x--- 2 mysql mysql     8192 Nov 15 14:47 sys
#人为将数据库数据的权限修改
[root@localhost ~]# ll /data/mysql/data
total 110644
-rw-r----- 1 mysql mysql       56 Nov 15 14:47 auto.cnf
-rw-r----- 1 mysql mysql      296 Nov 15 17:40 ib_buffer_pool
-rw-r----- 1 root  root  12582912 Nov 15 17:40 ibdata1
-rw-r----- 1 mysql mysql 50331648 Nov 15 17:40 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Nov 15 14:47 ib_logfile1
-rw-r----- 1 mysql mysql    14958 Nov 15 17:31 localhost.localdomain.err
drwxr-x--- 2 mysql mysql     4096 Nov 15 14:47 mysql
drwxr-x--- 2 mysql mysql     8192 Nov 15 14:47 performance_schema
drwxr-x--- 2 mysql mysql     8192 Nov 15 14:47 sys
#systemcctl启动,无显示，但无法登录
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
#查看数据库日志，会在数据目录下生成一个"主机名.err"的日志文件
[root@localhost data]# ls /data/mysql/data/localhost.localdomain.err
/data/mysql/data/localhost.localdomain.err
#日志信息（使用了/etc/init.d/mysqld start 产生的ERROR信息，7版本的启动没有这个信息）
2021-11-16T05:39:51.782376Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
2021-11-16T05:39:51.782446Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
2021-11-16T05:39:51.782456Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2021-11-16T05:39:52.413788Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2021-11-16T05:39:52.414053Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2021-11-16T05:39:52.414099Z 0 [ERROR] Failed to initialize builtin plugins.
2021-11-16T05:39:52.414133Z 0 [ERROR] Aborting
2021-11-16T05:39:52.414162Z 0 [Note] Binlog end
2021-11-16T05:39:52.414351Z 0 [Note] Shutting down plugin 'MyISAM'
2021-11-16T05:39:52.414427Z 0 [Note] Shutting down plugin 'CSV'
2021-11-16T05:39:52.415472Z 0 [Note] /application/mysql/bin/mysqld: Shutdown complete

五、数据库账户管理

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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
#设置用户密码
mysqladmin -uroot -p password **********
#管理员用户密码忘记了？
1、停止数据库
2、启动数据库到维护模式,然后登陆（跳过授权表以及远程登录）
mysqld_safe --skip-grant-tables --skip-networking &
3、查看数据库用户
mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
4、查看表的字段（倒叙显示）（省略了部分）
mysql> desc mysql.user;
+------------------------+----------------------------+------+-----+-----------------------+-------|
| Field                  | Type                       | Null | Key | Default               | Extra |
+------------------------+----------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                   | NO   | PRI |                       |       |
| User                   | char(32)                   | NO   | PRI |                       |       |
| authentication_string  | text                       | YES  |     | NULL                  |       |
5、查看到mysql的账户密码（加密的）
mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *BDC9C4356E3F936C3CD5845B75682E6B02555760 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
6、手工加载授权表,修改密码
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> grant all on *.* to root@'localhost' identified by '1';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *E6CC90B878B948C35E92B003C792C46C58C4AF40 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+

或者：alter user root@'localhost' identified by '12';
7、重启数据库即可。