Mysql5.7安装与创建测试数据
1.Mysql5.7安装
2.创建测试数据
一、环境准备：
1.目录创建：
mkdir -p /data/{software,mysql}
mkdir -p /data/mysql/{data,log,tmp}
2.用户创建：
useradd mysql
3.安装依赖包：
yum install perl perl-devel perl-Data-Dumper libaio-devel -y
二、安装：
1.下载包文件：
cd /data/software/
wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz
tar -zxvf mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz
ln -s /data/software/mysql-5.7.44-linux-glibc2.12-x86_64 /usr/local/mysql
2.权限赋值：
chown -R mysql:mysql /usr/local/mysql /data/mysql/*
三、配置文件：
参数可以根据实际需求进行调配
vi /etc/my.cnf
[client]
port = 3306
socket = /data/mysql/tmp/mysql.sock
[mysql]
prompt="\u@db \R:\m:\s [\d]> "
no-auto-rehash
[mysqld]
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql/data
socket = /data/mysql/tmp/mysql.sock
tmpdir =
pid-file = /data/mysql/tmp/mysql.pid
character-set-server=utf8
collation-server = utf8_general_ci
sql_mode=‘NO_UNSIGNED_SUBTRACTION,NO_ENGINE_SUBSTITUTION’
open_files_limit = 65535
innodb_open_files = 65535
back_log=1024
max_connections = 512
max_connect_errors=1000000
interactive_timeout=300
wait_timeout=300
max_allowed_packet = 1024M
secure_file_priv=’’
log-error=/data/mysql/log/error.log
slow_query_log=ON
slow_query_log_file=/data/mysql/log/slow_mysql.log
long_query_time=1
innodb_flush_log_at_trx_commit=1
innodb_log_file_size =1G
innodb_log_files_in_group=3
innodb_log_group_home_dir=./
sync_binlog = 1
binlog_cache_size = 16M
max_binlog_cache_size = 1G
max_binlog_size=1G
expire_logs_days = 30
log-bin-trust-function-creators=1
log-bin= /data/mysql/log/binlog-mysql
binlog_format=row
binlog_row_image=full
server-id = 1
# 大小根据实际情况而定
innodb_buffer_pool_size=4G
innodb_buffer_pool_instances=2
四、初始化Mysql
1.初始化操作：
/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql/data --user=mysql --initialize
2.查看随机密码：可以看到我们的初始密码为 “Eiv*/Dy!I44t”
[root@node1 data]# cat /data/mysql/log/error.log | grep password
2019-02-21T10:45:24.067928Z 1 [Note] A temporary password is generated for root@node1: Eiv*/Dy!I44t
五、启动mysql：
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &
六、修改密码，设置环境变量以及自启动
[root@node1 log]# echo ‘export PATH=/usr/local/mysql/bin:$PATH’ >> /etc/profile
[root@node1 ~]# source /etc/profile
[root@node1 ~]# /usr/local/mysql/bin/mysqladmin -uroot -p password
Enter password:
New password:
Confirm new password: P@ssw0rd
[root@node1 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@node1 ~]# chkconfig --add mysqld
[root@node1 ~]# chkconfig mysqld on
七、登陆测试
[root@node1 bin]# mysql -uroot -pP@ssw0rd
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (111)
解决：
创建软链接：ln -s /data/mysql/tmp/mysql.sock /tmp/mysql.sock
[root@node1 data]# mysql -uroot -pP@ssw0rd
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.44-log MySQL Community Server (GPL)
Copyright © 2000, 2023, Oracle and/or its affiliates.
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.
root@db 13:02: [(none)]>
[root@node1 tmp]# mysql -h172.17.0.35 -P3306 -uroot -pP@ssw0rd -e “create database if not exists sbtests”
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1130 (HY000): Host ‘mysqldb3’ is not allowed to connect to this MySQL server
解决：
#赋予远程访问权限
select host, user, authentication_string, plugin from mysql.user;
update mysql.user set host=’%’ where user=‘root’;
grant all privileges on *.* to root@’%’ with grant option;
mysql> select host, user, authentication_string, plugin from mysql.user;
±----------±--------------±------------------------------------------±----------------------+
| host | user | authentication_string | plugin |
±----------±--------------±------------------------------------------±----------------------+
| node1 | root | *8232A1298A49F710DBEE0B330C42EEC825D4190A | mysql_native_password |
| node1 | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
| node1 | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
±----------±--------------±------------------------------------------±----------------------+
3 rows in set (0.00 sec)
mysql> update mysql.user set host=’%’ where user=‘root’;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> grant all privileges on *.* to root@’%’ with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
#刷新权限
flush privileges;
#登陆
mysql -uroot -pP@ssw0rd -h172.17.0.35 -P3306
[root@node1 tmp]# mysql -uroot -pP@ssw0rd -h172.17.0.35 -P3306
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.44-log MySQL Community Server (GPL)
Copyright © 2000, 2023, Oracle and/or its affiliates.
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>
八、sysbench安装与创建测试数据
8.1 sysbench安装
yum -y install make automake libtool pkgconfig libaio-devel mariadb-devel openssl-devel
cd /data/software
unzip sysbench.zip
cd sysbench-master
./autogen.sh
./configure --prefix=/usr/local/sysbench/ --with-mysql --with-mysql-includes=/usr/local/mysql/include --with-mysql-libs=/usr/local/mysql/lib
make && make install
ln -s /usr/local/sysbench/bin/* /usr/bin/
ln -s /usr/local/sysbench/bin/* /usr/local/bin/
解决报错：
[root@node1 src]# ./sysbench --version
./sysbench: error while loading shared libraries: libmysqlclient.so.20: cannot open shared object file: No such file or directory
vim /etc/profile
增加环境变量
export LD_LIBRARY_PATH=/usr/local/mysql/lib:$LD_LIBRARY_PATH
source /etc/profile
[root@node1 sysbench-master]# sysbench --version
sysbench 1.1.0
8.2 创建测试数据
创建数据库
mysql -hhost−P{host} -Phost−P{port} -uuser−p{user} -puser−p{pass} -e "create database if not exists ${db} "
mysql -h172.17.0.35 -P3306 -uroot -pP@ssw0rd -e “create database if not exists sbtests”
模拟测试数据
cd /data/software/sysbench/sysbench-master/src/lua
sysbench oltp_common_noshard.lua --threads=2 --mysql-user=root --mysql-password=“P@ssw0rd” --mysql-host=172.17.0.35 --mysql-port=3306 --mysql-db=sbtests --report-interval=1 --table-size=100000 --tables=10 prepare
或者
[root@node1 lua]#
cd /data/software/sysbench/sysbench-master/src/lua
sysbench oltp_common_bigtab_noshard.lua --threads=2 --mysql-user=root --mysql-password=“Password123” --mysql-host=172.17.0.35 --mysql-port=3306 --mysql-db=sbtests --report-interval=1 --table-size=50000000 --tables=10 prepare
[root@node1 tmp]# mysql -uroot -pP@ssw0rd -h172.17.0.35 -P3306
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.44-log MySQL Community Server (GPL)
Copyright © 2000, 2023, Oracle and/or its affiliates.
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> use sbtests;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
±------------------+
| Tables_in_sbtests |
±------------------+
| sbtest1 |
| sbtest10 |
| sbtest2 |
| sbtest3 |
| sbtest4 |
| sbtest5 |
| sbtest6 |
| sbtest7 |
| sbtest8 |
| sbtest9 |
±------------------+
10 rows in set (0.00 sec)
mysql> select count(*) from sbtest1;
±---------+
| count(*) |
±---------+
| 100000 |
±---------+
1 row in set (0.02 sec)
mysql> select count(*) from sbtest10;
±---------+
| count(*) |
±---------+
| 100000 |
±---------+
1 row in set (0.01 sec)
到此，Mysql5.7安装与创建测试数据已经完成
