mysql5.7源码安装及常用命令
一、mysql安装
1.旧版本卸载
rpm -qa | grep “mariadb”
rpm -ev 软件包名称 –nodeps
2.安装依赖
yum -y install gcc gcc-c++ ncurses ncurses-devel cmake bison bison-devel
3.mysql下载
yum安装(可选)
yum install mariadb-server mariadb mariadb-libs -y
源码下载(推荐)
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.25.tar.gz
wget http://nchc.dl.sourceforge.net/project/boost/boost/1.59.0/boost_1_59_0.tar.gz
tar -xzvf boost_1_59_0.tar.gz
tar -xzvf mysql-5.7.25.tar.gz
4.创建mysql用户
useradd -s /sbin/nologin mysql
mkdir -p /data/mysql
chown -R mysql:mysql /data/mysql
5.cmake编译安装
cd mysql-5.7.25
cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DMYSQL_DATADIR=/data/mysql -DDOWNLOAD_BOOST=1 -DWITH_BOOST=../boost_1_59_0 -DSYSCONFDIR=/etc -DMYSQL_USER=mysql -DMYSQL_TCP_PORT=3306 -DWITH_XTRADB_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DENABLED_LOCAL_INFILE=1 -DWITH_EXTRA_CHARSETS=1 -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DEXTRA_CHARSETS=all -DWITH_BIG_TABLES=1 -DWITH_DEBUG=0
安装 make && make install
(也可使用make -j4 && make install 使用4个CPU提高速度，但是内存要足够，不然会报错)
5.添加环境变量
echo -e ‘
export PATH=/usr/local/mysql/bin:$PATH
‘ >> /etc/profile && source /etc/profile
6.添加mysql至systemd，开启自启动
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
systemctl enable mysqld
7.(可选)编辑配置文件
从5.7.18开始不在二进制包中提供my-default.cnf文件,所以不需要my.cnf也可以正常启动。，最好复制一份my.cnf使用
但是为了参数性能调优，则需要创建一个/etc/my.cnf,在官网上找一个配置文件进行修改,
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It‘s a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
basedir = /usr/local/mysql
datadir = /data/mysql
port = 3306
server_id = 1
socket = /tmp/mysql.sock
log-bin = mysql-bin
expire_logs_days = 10
max_binlog_size = 100M
#general_log = 1
#general_log_file = /usr/local/mysql/query.log
slow_query_log = 1
slow_query_log_file = slow-query.log
long-query_time = 2
log-error = error.log
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
# sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
8.初始化mysql
mysqld –initialize-insecure –user=mysql –basedir=/usr/local/mysql –datadir=/data/mysql
没有配置my.cnf显示告警如下，可以正常启动
注意：确保/data/mysql没有数据，有文件会报如下错误
9.启动mysql
systemctl start mysqld
正常运行
二、mysql常用命令
1.登录数据库
本地登录
mysql -u root -p
设置密码(无密码)
mysql -uroot -ppassword -e “set passowrd for root = password(‘passowrd‘)”
或者
mysqladmin -uroot password “NEWPASSWORD”
修改用户密码
update mysql.user set authentication_string=password(‘新密码‘) where User=‘用户‘ and Host=‘主机‘;
flush privileges;
忘记密码
mysqld_safe –skip-grant-tables &
# 也可在[ msyqld]选项中添加skip-grant-tables参数到/etc/my.cnf主配置文件中。(修改完要注释)
update mysql.user set authentication_string=password(‘新密码‘) where User=‘用户‘ and Host=‘主机‘;
flush privileges;
2.用户命令
select user(); #登录的用户
select user from mysql.user; # 数据库有哪些用户
create user ‘用户名‘@‘localhost‘ identified by ‘密码‘; # 创建本地用户
create user ‘用户名‘@‘%‘ identified by ‘密码‘; # 创建远程登录
delete from user where User=‘用户‘ and Host=‘主机‘;flush privileges;
或者drop user ‘用户名‘@ ‘localhost‘; # 删除用户
grant 权限 on 数据库.* to ‘用户名‘@‘登录主机‘ identified by ‘密码‘; #给用户添加权限
GRANT ALL PRIVILEGES ON *.* TO ’用户‘@‘登录主机’; #给用户所有权限
revoke select on 数据库.* from ‘用户’@‘登录主机‘; # 取消对所有数据库的查询权限
show grants for ‘用户‘@‘主机‘; # 查看账户 的权限
3.操作数据库
use 数据库名; 切换数据库
show databases; #显示数据库
show tables; #显示数据表
desc 数据表名; #显示数据表结构
desc 数据库名.数据表名;
show create table 表名 G； #显示表详细结构
show create database DB_NAME; #查看数据库字符集
select 字段名(多个字段用“,”隔开，*表示所有字段) from 数据表名; #查询数据库的表
create database 库名; #建库
drop database 库名; #删库
create table 表名(字段列表); #建表
drop table 表名; #删表
delete table 表名; #清空表
mysqladmin -uroot -p密码 shutdown # 关闭数据库
4.数据备份与导入
mysqldump -uroot -p密码 –databases dbname >/root/mysql_bak.sql #导出数据库
mysqldump -uroot -p密码 < /root/mysql_bak.sql –default-character-set=utf8 #导入数据库
mysql
source /root/back.bak
5.索引
ALTER TABLE t1 ADD PRIMARY KEY ( `column` )； # 主键索引 唯一不为空
ALTER TABLE t1 ADD UNIQUE (`column`)； #唯一索引 唯一可为空
ALTER TABLE t1 ADD INDEX index_name ( `column‘ )； #普通索引 使用广泛
ALTER TABLE t1 ADD FULLTEXT ( `column` )； #全文索引 用于MyISAM表，大量内容检索
ALTER TABLE t1 ADD INDEX index_name ( `column1`, `column2`, `column3` )；#组合索引 提高效率
6.配置远程连接mysql
GRANT ALL PRIVILEGES ON *.* TO ‘root‘@‘%‘ IDENTIFIED BY ‘Huawei12#$‘;
flush privileges;
正常登录
mysql5.7源码安装及常用命令