mysql 二进制格式安装

二进制格式mysql安装

下载二进制格式的mysql软件包

wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz

创建用户和组

[root@test ~]# useradd -r -M -s /sbin/nologin mysql



##### 解压软件至/usr/local/
[root@test ~]# ls
anaconda-ks.cfg  mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz
[root@test ~]# tar -xf mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz  -C /usr/local/
[root@test ~]# cd /usr/local/
[root@test local]# ls
bin  games    lib    libexec                              sbin   src
etc  include  lib64  mysql-5.7.31-linux-glibc2.12-x86_64  share

[root@test local]# ln -sv mysql-5.7.31-linux-glibc2.12-x86_64/  mysql
'mysql' -> 'mysql-5.7.31-linux-glibc2.12-x86_64/'

##### 修改目录/usr/local/mysql的所有者和所属组
[root@test local]# chown -R mysql.mysql mysql
[root@test local]# ll -d mysql
lrwxrwxrwx. 1 mysql mysql 36 Dec 29 01:40 mysql -> mysql-5.7.31-linux-glibc2.12-x86_64/

##### 添加环境变量，头文件，lib库文件，man帮助等。
[root@test local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@test local]# source  /etc/profile.d/mysql.sh
[root@test local]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

[root@test local]#  vim /etc/profile.d/mysql.sh
[root@test local]# ln -sv  /usr/local/mysql/include/    /usr/include/mysql
'/usr/include/mysql' -> '/usr/local/mysql/include/'
[root@test local]# vim /etc/ld.so.conf.d/mysql.conf
[root@test local]# ldconfig
[root@test local]# vim /etc/man_db.conf
[root@test local]# chown -R mysql.mysql /opt/data 
chown: cannot access '/opt/data': No such file or directory



##### 建立数据存放目录
[root@test local]# mkdir /opt/data
[root@test local]# chown -R mysql.mysql /opt/data 




##### 初始化数据库
[root@test local]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/
2020-12-28T17:45:14.091697Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-12-28T17:45:14.301326Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-12-28T17:45:14.329385Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-12-28T17:45:14.389119Z 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: 70c3107f-4934-11eb-b126-000c2989b3b5.
2020-12-28T17:45:14.389750Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-12-28T17:45:15.300391Z 0 [Warning] CA certificate ca.pem is self signed.
2020-12-28T17:45:15.900269Z 1 [Note] A temporary password is generated for root@localhost: U7<Wm?7Et=3o

这个命令的最后会生成一个临时密码，此处密码是 U7<Wm?7Et=3o

生成配置文件

[root@test local]# cat > /etc/my.cnf <<EOF
> [mysqld]
> basedir = /usr/local/mysql
> datadir = /opt/data
> socket = /tmp/mysql.sock
> port = 3306
> pid-file = /opt/data/mysql.pid
> user = mysql
> skip-name-resolve
> EOF
[root@test local]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve

配置服务启动脚本

[root@test local]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@test local]# sed -ri 's#^(basedir=).*#\1/usr/local/mysql#g' /etc/init.d/mysqld
[root@test local]# sed -ri 's#^(datadir=).*#\1/opt/data#g' /etc/init.d/mysqld
[root@test local]# ls /usr/local/mysql/support-files/
magic  mysqld_multi.server  mysql-log-rotate  mysql.server
[root@test local]# ll /etc/init.d/mysqld   
-rwxr-xr-x. 1 7161 31415 10601 Dec 29 01:58 /etc/init.d/mysqld
[root@test local]# vim /etc/init.d/mysqld

启动mysql

[root@test local]# service mysqld start
Starting MySQL.Logging to '/opt/data/test.err'.
 SUCCESS! 
[root@test local]# ss  -antl
State       Recv-Q       Send-Q              Local Address:Port             Peer Address:Port      
LISTEN      0            128                       0.0.0.0:22                    0.0.0.0:*         
LISTEN      0            128                          [::]:22                       [::]:*         
LISTEN      0            80                              *:3306                        *:*

[root@testt ~]# mysql -uroot -pU7<Wm?7Et=3o
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.31
 
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> set password = password('mei123')
    -> ;
Query OK, 0 rows affected, 1 warning (0.00 sec

 

2. mysql配置文件

mysql的配置文件为/etc/my.cnf

配置文件查找次序：若在多个配置文件中均有设定，则最后找到的最终生效

/etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE --> ~/.my.cnf

mysql常用配置文件参数：

参数	说明
port = 3306	设置监听端口
socket = /tmp/mysql.sock	指定套接字文件位置
basedir = /usr/local/mysql	指定MySQL的安装路径
datadir = /data/mysql	指定MySQL的数据存放路径
pid-file = /data/mysql/mysql.pid	指定进程ID文件存放路径
user = mysql	指定MySQL以什么用户的身份提供服务
skip-name-resolve	禁止MySQL对外部连接进行DNS解析 使用这一选项可以消除MySQL进行DNS解析的时间。 若开启该选项，则所有远程主机连接授权都要使用IP地址方 式否则MySQL将无法正常处理连接请求

 

 

3. mysql数据库备份与恢复

3.1 数据库常用备份方案

数据库备份方案：

• 全量备份
• 增量备份
• 差异备份

备份方案	特点
全量备份	全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。 数据恢复快。 备份时间长
增量备份	增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份 与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象 是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量 备份后所产生的增加和修改的文件，如此类推。 没有重复的备份数据 备份时间短 恢复数据时必须按一定的顺序进行
差异备份	备份上一次的完全备份后发生变化的所有文件。 差异备份是指在一次全备份后到进行差异备份的这段时间内 对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。