mysql 5.7原码安装_mysql5.7源码安装及常用命令

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 '\n\nexport PATH=/usr/local/mysql/bin:$PATH\n' >> /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;

正常登录