文章目录
更改yum源与更新系统。
直接打开 163 源网站:http://mirrors.163.com/.help/centos.html
按照使用说明,还是先备份一下源(使用下面的命令重命名原来的源,如果有错误,再改回来):
mv /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.backup
转到源目录:
cd /etc/yum.repos.d/
阿里云源:
wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
运行以下命令生成缓存:
yum clean all
yum makecache
MySQL5.7源配置及系统的安装
在MySQL官网中下载YUM源rpm安装包:http://dev.mysql.com/downloads/repo/yum/
MySQL YUM源下载地址
下载mysql源安装包
# wget http://repo.mysql.com/mysql57-community-release-el7-8.noarch.rpm
安装mysql源
# yum localinstall mysql57-community-release-el7-8.noarch.rpm
检查mysql源是否安装成功
# yum repolist enabled|grep "mysql.*-community.*"
mysql-connectors-community/x86_64 MySQL Connectors Community 42
mysql-tools-community/x86_64 MySQL Tools Community 55
mysql57-community/x86_64 MySQL 5.7 Community Server 247
安装mysql
# yum install mysql-server
启动mysql
# systemctl start mysqld
# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 一 2018-01-29 07:55:20 CST; 3s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 2803 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 2717 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 2806 (mysqld)
CGroup: /system.slice/mysqld.service
└─2806 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
开机启动
# systemctl enable mysqld
# systemctl daemon-reload
修改root本地登录密码
mysql安装完成之后,在/var/log/mysqld.log文件中给root生成了一个默认密码。通过下面的方式找到root默认密码,然后登录mysql进行修改:
# grep 'temporary password' /var/log/mysqld.log
# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.21
Copyright (c) 2000, 2018, 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 for root@localhost = password('123');
忘记密码
1、修改配置文件my.cnf 按i编辑
# vim /etc/my.cnf
在[mysqld]中添加
skip-grant-tables
例如:
[mysqld]
**skip-grant-tables**
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
键盘 Esc 保存修改 :wq退出
2、重启mysql服务
systemctl restart mysqld
3、用户登录
mysql -uroot -p (直接点击回车,密码为空)
选择数据库
use mysql;
下面我们就要修改密码了
以前的版本我们用的是以下修改
update user set authentication_string=password('123456') where user='root';
执行
flush privileges;
4、退出mysql
quit;
5、将最开始修改的配置文件my.cnf中的skip-grant-tables删除
6、重启mysql
修改本地密码
alter user root@localhost identified by 'yourpasswordhere';
注意:mysql5.7默认安装了密码安全检查插件(validate_password),默认密码检查策略要求密码必须包含:大小写字母、数字和特殊符号,并且长度不能少于8位。否则会提示ERROR 1819 (HY000): Your password does not satisfy the current policy requirements错误
通过msyql环境变量可以查看密码策略的相关信息
show variables like '%password%';
+---------------------------------------+--------+
| Variable_name | Value |
+---------------------------------------+--------+
| default_password_lifetime | 0 |
| disconnect_on_expired_password | ON |
| log_builtin_as_identified_by_password | OFF |
| mysql_native_password_proxy_users | OFF |
| old_passwords | 0 |
| report_password | |
| sha256_password_proxy_users | OFF |
| validate_password_check_user_name | OFF |
| validate_password_dictionary_file | |
| validate_password_length | 8 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | MEDIUM |
| validate_password_special_char_count | 1 |
+---------------------------------------+--------+
14 rows in set (0.01 sec)
添加远程登录用户
默认只允许root帐户在本地登录,如果要在其它机器上连接mysql,必须修改root允许远程连接,或者添加一个允许远程连接的帐户,为了安全起见,我添加一个新的帐户:
mysql> GRANT ALL PRIVILEGES ON *.* TO newuser1@'%' IDENTIFIED BY 'newpassword' WITH GRANT OPTION;
配置默认编码为utf8
修改前
mysql> show variables like '%character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)
修改/etc/my.cnf配置文件,在[mysqld]下添加编码配置,如下所示:
[mysqld]
character_set_server=utf8
init_connect=‘SET NAMES utf8’
重新启动mysql服务,查看数据库默认编码如下所示
mysql> show variables like '%character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
默认配置文件路径:
配置文件:/etc/my.cnf
日志文件:/var/log//var/log/mysqld.log
服务启动脚本:/usr/lib/systemd/system/mysqld.service
socket文件:/var/run/mysqld/mysqld.pid
mysql客户端开发环境安装
yum install mysql-community-devel
安装后生成/usr/include/mysql目录,内含mysql开发c头文件,开发中可引用。
完全卸载MySQL
查看已经安装的服务
rpm –qa|grep -i mysql
-i 作用是不区分大小写
yum remove mysql mysql-server mysql-libs compat-mysql51
rm -rf /var/lib/mysql
rm /etc/my.cnf
查看是否还有mysql软件:
rpm -qa|grep mysql
有的话继续删除
可以看到有两个安装包
MySQL-server-5.6.19-1.linux_glibc2.5.x86_64.rpm
MySQL-client-5.6.19-1.linux_glibc2.5.x86_64.rpm
删除这两个服务(去掉后缀)
rpm –e MySQL-client-5.6.19-1.linux_glibc2.5.x86_64
rpm -e MySQL-server-5.6.19-1.linux_glibc2.5.x86_64
查看残留的目录:
whereis mysql
然后删除mysql目录:
rm –rf /usr/lib64/mysql
删除相关文件:
rm –rf /usr/my.cnf
rm -rf /root/.mysql_sercret
最关键的:
rm -rf /var/lib/mysql
如果这个目录如果不删除,再重新安装之后,密码还是之前的密码,不会重新初始化!
数据导出和导入
导出
使用mysqldump命令行下具体用法如下:
mysqldump -u用户名 -p密码 -d 数据库名 表名 > 脚本名;
1、导出数据库为dbname的表结构(其中用户名root,密码为dbpasswd,生成的脚本名為db.sql)
mysqldump -uroot -pdbpasswd -d dbname >db.sql;
2、导出数据库为dbname的一张表tableName表结构。
mysqldump -uroot -pdbpasswd -d dbname tableName>db.sql;
3、导出数据库为dbname所有表结构及表数据(不加-d)
mysqldump -uroot -p --databases dbname >db.sql;
4、导出数据库为dbname的一张表tableName结构及表数据(不加-d)
mysqldump -uroot -pdbpasswd dbname tableName >db.sql;
导入
1、首先建空数据库
mysql>create database abc;
2、导入数据库
方法一:
(1)选择数据库
mysql>use abc;
(2)设置数据库编码
mysql>set names utf8;
(3)导入数据(注意sql文件的路径)
mysql>source /home/abc/abc.sql;
方法二:
mysql -u用户名 -p 数据库名 < 数据库名.sql