[sql]mysql5.6cmake安装/mysql5.7二进制安装
centos7上cmake编译安装mysql-5.6.36.tar.gz
系统环境
- 环境(安装前规划好主机名,mysql编译过程会用)
[root@n1 mysql-5.6.36]# cat /etc/redhat-release
CentOS Linux release 7.4.1708 (Core)
- 关掉防火墙和selinux
cmake编译环境
# cmake编译环境
yum install -y gcc gcc-c++
yum install -y cmake
# mysql依赖
yum install -y ncurses-devel libaio-devel
编译安装mysql-5.6.36.tar.gz
- 下载解压
cd /usr/local/src/
wget -q http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.36.tar.gz
tar xf mysql-5.6.36.tar.gz
- cmake进行编译，安装，创建软连接
cd mysql-5.6.36
cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.6.36 \
-DMYSQL_DATADIR=/usr/local/mysql-5.6.36/data \
-DMYSQL_UNIX_ADDR=/usr/local/mysql-5.6.36/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITH_ZLIB=bundled \
-DWITH_SSL=bundled \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DENABLE_DOWNLOADS=1 \
-DWITH_DEBUG=0
make -j 4 && make install #这里我有4核,为了快一些
ln -s /usr/local/mysql-5.6.36/ /usr/local/mysql
编译报错解决
- cmake时候提示找不到cxx
compiler identification is unknown-- The CXX compiler identification is unknown
yum install -y gcc gcc-c++
rm -f CMakeCache.txt #干掉(一定要干掉)重新编译
善后mysql环境
- 准备my.cnf
\cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf
- 初始化db
/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data --user=mysql
- xx/tmp/mysql.sock,原因是5.6.36版本不会自动创建tmp目录
mkdir /usr/local/mysql/tmp
- 更改目录权限
useradd -M -s /sbin/nologin -u 1000 mysql
chown -R mysql.mysql /usr/local/mysql
chown -R mysql.mysql /usr/local/mysql/
- 开机启动
cp support-files/mysql.server /etc/init.d/mysqld
chmod 700 /etc/init.d/mysqld #使用这个脚本可以生成
- PATH优化
echo 'PATH=/usr/local/mysql/bin/:$PATH' >>/etc/profile
tail -1 /etc/profile
source /etc/profile
echo $PATH
mysql
- 可能遇到的问题
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/usr/local/mysql-5.6.36/tmp/mysql.sock' (2)
解决: /etc/init.d/mysqld 这种方式启动,可以生成mysql.sock,并且能成功启动
- 设置root密码
mysqladmin -u root password '123456'
mysql -uroot -p123456
- 优化数据库
select user,host from mysql.user;
drop user ''@'n1.ma.com';
drop user ''@'localhost';
drop user 'root'@'n1.ma.com';
drop user 'root'@'::1';
select user,host from mysql.user;
drop database test;
show databases;
- 留下这两个即可
mysql> select user,host from mysql.user;
+------+-----------+
| user | host |
+------+-----------+
| root | 127.0.0.1 |
| root | localhost |
+------+-----------+
- 查看连接数-字符集-存储引擎
mysql> show variables like "%max_con%";
+---------------------------------------+-------+
| Variable_name | Value |
+---------------------------------------+-------+
| max_connect_errors | 100 |
| max_connections | 151 |
| performance_schema_max_cond_classes | 80 |
| performance_schema_max_cond_instances | 3504 |
+---------------------------------------+-------+
4 rows in set (0.00 sec)
mysql> show variables like "%char%";
+--------------------------+-----------------------------------------+
| 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/local/mysql-5.6.36/share/charsets/ |
+--------------------------+-----------------------------------------+
8 rows in set (0.00 sec)
mysql> show variables like "%engine%";
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| storage_engine | InnoDB |
+----------------------------+--------+
3 rows in set (0.00 sec)
小结
- 初始化命令
mysql_install_db --basedir=/application/mysql --datadir=/application/mysql/data/ --user=mysql
- 会提示设置密码的方法
/usr/local/mysql/bin/mysqladmin -u root password 'new-password'
/usr/local/mysql/bin/mysqladmin -u root -h n1.ma.com password 'new-password'
- 会提示启动mysql方法
cd . ; /usr/local/mysql/bin/mysqld_safe &
centos7上二进制安装mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz
系统环境
- 环境
[root@n1 mysql-5.6.36]# cat /etc/redhat-release
CentOS Linux release 7.4.1708 (Core)
- 关掉防火墙和selinux
下载-安装-初始化(5.6版本二进制安装时候需要)
初始化mysql数据库提示缺少Data:dumper模块解决方法
yum install autoconf -y
- 下载
cd /usr/local/src/
wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz
- 解压-移动-软链接
tar xf mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz
mv mysql-5.7.17-linux-glibc2.5-x86_64 /usr/local/mysql-5.7.17
ln -s /usr/local/mysql-5.7.17 /usr/local/mysql
- 添加mysql用户，并进行授权
useradd -M -s /sbin/nologin -u 1000 mysql
chown -R mysql.mysql /usr/local/mysql
chown -R mysql.mysql /usr/local/mysql/
- 初始化数据库
/usr/local/mysql-5.7.17/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql-5.7.17 --datadir=/usr/local/mysql-5.7.17/data
#注意这里会初始化生成一个root的密码,后面需要改掉
- 拷贝配置文件
\cp /usr/local/mysql-5.7.17/support-files/my-default.cnf /etc/my.cnf
- 启动(适用于centos6)
\cp /usr/local/mysql-5.7.17/support-files/mysql.server /etc/init.d/mysqld
/etc/init.d/mysqld start
- 添加env
echo 'PATH=/usr/local/mysql/bin/:$PATH' >>/etc/profile
tail -1 /etc/profile
source /etc/profile
echo $PATH
- 修改mysql密码
[root@n1 ]# mysqladmin -uroot -p password 123456
Enter password: 【输入初始化时生成的密码】
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
mysql日常维护
初始化db:
(5.6)mysql_install_db:
mysql_install_db --basedir=/application/mysql --datadir=/application/mysql/data/ --user=mysql
(5.7)mysqld:
mysqld --initialize --user=mysql --basedir=/usr/local/mysql-5.7.17 --datadir=/usr/local/mysql-5.7.17/data
mysqld_safe:
启动mysql(msyql.server --> mysql_safe --> mysqld): /usr/local/mysql/bin/mysqld_safe &
恢复密码: /usr/local/mysql/bin/mysqld_safe --skip-grant-table &
mysqladmin
设置密码: /usr/local/mysql/bin/mysqladmin -u root password '123456';
修改密码: mysqladmin -uroot -p123456 password 'maotai123';
(sql语句密码函数): update mysql.user set password=PASSWORD('新密码') where User='root'; flush privileges;
创建用户(指定密码)并授权
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
- 查帮助: help grant
- 建库/建表/插数据
- 查表结构
desc student;
show create table student;
- 查看慢询开关
show variables like '%slow%';
[mysqld]
Log_slow_queries = ON # 开启慢查询服务
Log-slow-queries = /var/log/slowqueries.log # 慢查询日志存储路径。
Long_query_time = 1 # 定义慢查询时间长度，默认为10
yum安装mysql5.6
- 安装mysql
rpm -ivh http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm
修改mysql配置:
[mysqld]
default-storage-engine = innodb
innodb_file_per_table
collation-server = utf8_general_ci
init-connect = 'SET NAMES utf8'
character-set-server = utf8
- 查看mysql版本:
方法1:
./mysqld -v
方法2:(3种)
select version();
show status;
/s
- mysql(5.6)编译参数：
cat "/usr/local/mysql/bin/mysqlbug"|grep configure
- 查看当前执行的sql
mysql> show processlist;
mysql> show full processlist;
docker run \
-p 3306:3306 \
-v /data/mysql:/var/lib/mysql \
-v /etc/localtime:/etc/localtime \
--name mysql5 \
--restart=always \
-e MYSQL_ROOT_PASSWORD=123456 \
-d mysql:5.6.23 --character-set-server=utf8 --collation-server=utf8_general_ci
