MySQL安装以及客户端的使用

左手的ㄟ右手 2022-12-23 12:26 168阅读 0赞

### MySQL安装以及客户端的使用 ###

*  安装MySQL
 *  MySQL客户端安装
 *   *  修改用户密码:
     *  新建库
     *  新建表
 *  数据库备份

# 安装MySQL #

下载MySQLyum源

wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm

创建MySQLyum源

rpm -ivh mysql-community-release-el7-5.noarch.rpm

安装MySQL服务端

yum -y install mysql-server

设置开机启动

systemctl enable mysqld && systemctl start mysqld

字符集的配置:

vim /etc/my.cnf
     
    default-character-set = utf8
    [mysql.server]
    default-character-set = utf8
    [client]
    default-character-set = utf8
    
    [mysql.server]
    default-character-set = utf8
    [client]
    default-character-set = utf8
    [mysqld]
    # 设置禁用dns解析，但是，这样不能在mysql的授权表中使用主机名了，只能使用IP
    skip-name-resolve
    [mysqld]
    # #设置连接超时时间，默认28800
    wait_timeout=900
    # #设置不区分大小写，
    # #1表名存储在磁盘是小写的，但是比较的时候是不区分大小写
    # #0表名存储为给定的大小写，比较时区分大小写 
    # #2表名存储为给定的大小写，但是比较的时候是小写的
    lower_case_table_names=1
    #
    # #设置最大连接数
    max_connections = 1024
    # #设置缓冲池大小，如果是专服，一般设置成系统内存的70-80%
    innodb_buffer_pool_size = 12G
    # #设置缓冲池实例，范围1~64
    innodb_buffer_pool_instances = 6

给root用户设置密码并授权，默认密码为空
    运行命令行：mysql -u root -p
    运行命令行：grant all privileges on *.* to root@'%' identified by 'root' with GRANT OPTION;

# MySQL客户端安装 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center]  
   
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 1]

## 修改用户密码: ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 2]  
   
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 3]  
   
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 4]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 5]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 6]  
   
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 7]

## 新建库 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 8]  
   
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 9]

数据库名:jobs  
字符集:utf8 – UTF-8 Unicode  
排序规则:utf8\_general\_ci  
填写完成后点击确定，双击jobs 如图说明数据库创建成功.  
   
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 10]

## 新建表 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 11]  
   
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 12]  
   
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 13]

# 数据库备份 #

vim /root/bakmysql.sh

#!/bin/bash
    # Name: bakmysql.sh
    # THIS IS A ShellScript For Auto DB Backup and Delete old Backup
    backupdir=/storage/mysqlbak
    time=`date +%Y%m%d%H%M%S`
    mysqldump -hlocalhost -uLWPQ_USR -plwpq_sjzxpwd SJZXLWPQ > $backupdir/sjzxlwpq$time.sql
    find $backupdir -name "sjzxlwpq*.sql" -type f -mtime+15 -exec rm { }\; > /dev/null 2>&1

脚本说明：/storage/mysqlbak定义的数据库备份路径，可自定义，文件系统中必须含有此文件夹  
\-uLWPQ\_USR是指定数据库用户名称  
\-plwpq\_sjzxpwd 是指定数据库用户密码  
SJZXLWPQ是指定需要备份的数据  
sjzxlwpq$time.sql是定义数据库的名称  
创建计划任务  
运行命令行：vim /etc/crontab  
30 21 \* \* \* root sh /root/bakmysql.sh  
说明：每天21:30分执行一次备份

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/c855859c87d24429803688474bf8bdab.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221120/0e665ebea6884f18b5a2a145c95962fa.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221120/c04b80bd23d04f239a6bb55edab0c69e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20221120/c0073971321b4586a0805fd30b8c87b5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20221120/cf615af5b9444f83bc5dde903f50f029.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 5]: /images/20221120/6b98d4e7194c49b5b9f59e19000895d9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 6]: /images/20221120/62bd848e95d24ea18678f1bfc76c1bc3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 7]: /images/20221120/94d56d8456714e0fab3785f35f021f19.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 8]: /images/20221120/0f8f93c69e8944c48846fa19ea387604.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 9]: /images/20221120/dde8951fed5849e7beb21aab39e96d05.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 10]: /images/20221120/b1204d8bb8c64f268d7cebaa2ca2bee1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 11]: /images/20221120/dceb4b2ef19b48ffa680f602f3e3773b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 12]: /images/20221120/19dc920397824c2681b0995e8f1f32ed.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 13]: /images/20221120/99c8398f73594dbdbe5e20b1d1a65402.png