mysql安装

mysql安装

mysql三条产品线：

3.26-5.2（正宗本源后代）

5.4-6.0更高（把社区的优秀架构和产品拿过来）使用的最多的版本是5.7

mysql-cluster（mysql的集群产品线，互联网公司几乎不用）

mysql 下载地址：

http://mirrors.163.com/mysql/Downloads/MySQL-5.7/

#1、创建用户
useradd mysql -s /sbin/nologin -M
id mysql
​
#2、创建目录上传软件
mkdir -p /server/tools
cd /server/tools
​
#3、解压安装
tar xf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
mkdir -p /application
mv mysql-5.7.26-linux-glibc2.12-x86_64 /application/mysql-5.7.26
ln -s /application/mysql-5.7.26  /application/mysql
​
#4、配置配置文件
rpm -e --nodeps mariadb-libs
cat  >/etc/my.cnf<
[mysqld]
user = mysql
basedir = /opt/application/mysql/
datadir = /opt/application/mysql/data
socket = /tmp/mysql.sock
server_id = 6
port = 3306
log_error = /opt/application/mysql/data/mysql.err
[mysql]
socket = /tmp/mysql.sock
EOF
​
#5、初始化数据库
rpm -qa mariadb-libs
yum install libaio-devel -y
mkdir -p /opt/application/mysql/data
chown -R mysql.mysql /opt/application/mysql/
/opt/application/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/opt/application/mysql/ --datadir=/opt/application/mysql/data
​
#6、配置启动
#方法1：(systemd)
cat >/etc/systemd/system/mysqld.service<
[Unit]
Description=MySQL Server by oldboy
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/opt/application/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
EOF
​
systemctl start mysqld
systemctl enable mysqld
netstat -lntup|grep mysql
#方法2：(sys-v)
#cd /application/mysql/support-files/
#./mysql.server start
cp /application/mysql/support-files/mysql.server /etc/init.d/mysqld
/etc/init.d/mysqld start
​
​
#7、登录测试
echo 'export PATH="/opt/application/mysql/bin:$PATH"' >> /etc/profile
source /etc/profile
mysql
exit
​
#8、修改密码
mysqladmin -u root password '123456'
​
#9、登录测试
mysql -uroot -p123456
quit
​
说明： 
--initialize 参数：
对于密码复杂度进行定制：12位，4种
密码过期时间：180
给root@localhost用户设置临时密码
[root@db01 ~]# mysqld --initialize --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data
2019-06-13T04:21:27.706564Z 1 [Note] A temporary password is generated for root@localhost: =mrV)_16is4U
--initialize-insecure 参数：
无限制，无临时密码

常见操作

用户管理

# 创建用户
create user oldguo@'10.0.0.%' identified by '123';
​
# 查询用户
select user,host ,authentication_string from mysql.user;
​
# 删除用户
drop user oldguo@'10.0.0.%';
​
# 修改用户
alter user root@'localhost' identified by '123';
​
# 授权库给用户
grant all    on     *.* to    wordpress@'10.0.0.%'   identified by '123';
grant all    on     wordpress.* to    wordpress@'10.0.0.%'   identified by '123';
grant select,update,delete ,insert on zhihu.* to zhihu@'10.0.0.%' identified by '123';
​
# 查看用户权限
show grants for root@'localhost';
​
# 回收权限
revoke delete on zhihu.* from 'zhihu'@'10.0.0.%';
​
# 查看当前登录用户
select user();
​
# 设置密码
set password='新密码'；
​
# 修改密码
mysqladmin -u root -p666666 password '123456'
​
# mysql的连接方式
TCP/IP：mysql -uroot -poldboy123 -h 10.0.0.51 -P3306
Socket：mysql -uroot -poldboy123 -S /tmp/mysql.sock

备份与恢复

# 备份
mysqldump -uroot -p123  -A -R --triggers -E --master-data=2 --single-transaction >/data/backup/full.sql
​
# 恢复
mysql> set sql_log_bin=0;
mysql> source /data/backup/full.sql 
mysql> flush privileges;