mysql---普通用户安装MySQL
普通用户安装MySQL
一、解压
cd soft
tar -zxvf mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz
二、创建目录和移动
mkdir /home/XXX/usr/local
mkdir /home/XXX/usr/local/data
mkdir /home/XXX/usr/local/log
mv mysql-5.7.25-linux-glibc2.12-x86_64 /home/XXX/usr/local/mysql
三、新建配置文件my.cnf； 需要修改端口号；
（如果root用户安装的mysql占用了3306，那么普通用户需要修改端口号；这里端口号“port=3336”。）
cd /home/XXX/usr/local/mysql/
vi my.cnf
+++++++++++++my.cnf++++++++++++++++++++++++
[client]
port=3336
socket=/home/XXX/usr/local/mysql/mysql.sock
[mysqld]
port=3336
#设置mysql的安装目录
basedir=/home/XXX/usr/local/mysql
# 设置mysql数据库的数据的存放目录
datadir=/home/XXX/usr/local/data
pid-file=/home/XXX/usr/local/mysql/mysql.pid
socket=/home/XXX/usr/local/mysql/mysql.sock
log_error=/home/XXX/usr/local/log/error.log
collation_server=utf8_general_ci
# 服务端使用的字符集默认为8比特编码的latin1字符集
character_set_server=utf8
log-bin=/home/XXX/usr/local/log/mysql-bin
server-id=100
++++++++++++++++my.cnf++++++++++++++++++++
四、安装/初始化数据库
cd /home/XXX/usr/local/mysql
./bin/mysqld --defaults-file=/home/XXX/usr/local/mysql/my.cnf --initialize --user=XXX --basedir=/home/XXX/usr/local/mysql --datadir=/home/XXX/usr/local/data
五、获取root密码
cat /home/XXX/usr/local/log/error.log |grep root@localhost
六、启动mysql
cd /home/XXX/usr/local/mysql/bin/
./mysqld_safe --defaults-file=/home/XXX/usr/local/mysql/my.cnf --user=XXX &
七、登录mysql
cd /home/XXX/usr/local/mysql/bin/
$ ./mysql -uroot -P3336 -p -S /home/XXX/usr/local/mysql/mysql.sock
八、登录后，执行命令修改root密码（mysql@123） 及远程登录设置 ；
alter user 'root'@'localhost' identified by 'mysql@123';
grant all privileges on *.* to 'root'@'%' identified by 'mysql@123' with grant option; #或指定网段登录
use mysql;
select host,user from user;
+-----------+---------------+
| host | user |
+-----------+---------------+
| % | root |
+-----------+---------------+
FLUSH PRIVILEGES;
九、测试远程登录
netstat -ntpl |grep 3336
mysql -u root -h XXX.XXX.XXX.XXX(主机IP) -P 3336 -p
+++++++++++++++++++++++++++++++++++++++++++++
报错：ERROR 2003 (HY000): Can't connect to MySQL server
原因：没有开通防火墙或者关闭防火墙.
十、开通本地防火墙策略（需要root权限）
vi /etc/sysconfig/iptables
添加一条：
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3336 -j ACCEPT
重启防火墙
service iptables restart
####################
补充1：修改密码
5.7mysql
修改密码：
vi /etc/my.cof
[mysqld]
skip-grant-tables
进入数据库
#mysql -uroot -p
#
mysql> use mysql;
mysql> update user set authentication_string=password('Huaaa12#$') where user='root';
mysql>flush privileges;
补充2：创建用户，权限只读；
创建用户，并授权查询权限；
注意： zabbix 可以修改成* ，代表所有数据库； zabbix代表只读zabbix；
MariaDB [mysql]>use mysql;
MariaDB [mysql]> GRANT SELECT ON zabbix.* TO 'user01'@'%' IDENTIFIED BY "user01";
MariaDB [mysql]> FLUSH PRIVILEGES;
MariaDB [mysql]> GRANT SELECT ON zabbix.* TO 'user01'@'%' IDENTIFIED BY "user01"; Query OK, 0 rows affected (0.01 sec) MariaDB [mysql]> select host,user from user; +-----------+-----------+ | host | user | +-----------+-----------+ | % | root | | % | test_user | | % | user01 | | % | zabbix | | 127.0.0.1 | root | | ::1 | root | +-----------+-----------+ 6 rows in set (0.00 sec) MariaDB [mysql]> FLUSH PRIVILEGES; # [root@zabbix_server ~]# mysql -uuser01 -puser01 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 30 Server version: 5.5.60-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | zabbix | +--------------------+ 2 rows in set (0.00 sec) MariaDB [(none)]> use zabbix; 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 MariaDB [zabbix]> create table t1( -> id int, -> name char(2), -> age tinyint unsigned -> )default charset=utf8; ERROR 1142 (42000): CREATE command denied to user 'user01'@'localhost' for table 't1' MariaDB [zabbix]> drop table users; ERROR 1142 (42000): DROP command denied to user 'user01'@'localhost' for table 'users'
补充3：mysql 备份数据
每小时备份一次
[root@test01 homeward]# cat homeward_sql_backup.sh #!/bin/bash mysqldump -h 192.168.X.X -uroot -pmysqlXXX homeward > /homeward/homeward_`date +%Y-%m-%d_%H-%M-%S`.sql
每天删除前一天的备份数据
[root@test01 homeward]# cat del_old_sql_data.sh #/bin/bash find /homeward -mtime +1 -name "*.sql" -exec rm -rf {} \;
定时任务
[root@test01 xxx]# crontab -l #homeward_mysql_backup 0 * * * * /bin/bash /testdir/hometest_sql_backup.sh #del_old_mysql */30 * * * * /bin/bash /hometest/del_old_sql_data.sh
补充4： mysql 还原数据库
（1）先登录数据库
MariaDB >create database test_database;
MariaDB >use test_database;
MariaDB [test_database]>source /tmp/xxxx.sql; (还原数据库)
MariaDB [test_database]>show databases;
MariaDB [test_database]>show tables;
补充5： 使用docker安装mysql
参考链接：https://www.cnblogs.com/sablier/p/11605606.html
使用docker安装mysql
拉取官方镜像（我们这里选择5.7，如果不写后面的版本号则会自动拉取最新版） Copy docker pull mysql:5.7 # 拉取 mysql 5.7 docker pull mysql # 拉取最新版mysql镜像 MySQL文档地址 检查是否拉取成功 Copy $ sudo docker images 一般来说数据库容器不需要建立目录映射 Copy sudo docker run -p 3306:3306 --name mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7 –name：容器名，此处命名为mysql -e：配置信息，此处配置mysql的root用户的登陆密码 -p：端口映射，此处映射 主机3306端口 到 容器的3306端口 如果要建立目录映射 Copy duso docker run -p 3306:3306 --name mysql \ -v /usr/local/docker/mysql/conf:/etc/mysql \ -v /usr/local/docker/mysql/logs:/var/log/mysql \ -v /usr/local/docker/mysql/data:/var/lib/mysql \ -e MYSQL_ROOT_PASSWORD=123456 \ -d mysql:5.7 -v：主机和容器的目录映射关系，":"前为主机目录，之后为容器目录 检查容器是否正确运行 Copy docker container ls 可以看到容器ID，容器的源镜像，启动命令，创建时间，状态，端口映射信息，容器名字 进入docker本地连接mysql客户端 Copy sudo docker exec -it mysql bash mysql -uroot -p123456
补充6：获取mysql中最大的几张表
参考链接：https://my.oschina.net/php7/blog/1631825
SELECT table_name, table_size/1024/1024 FROM ( SELECT table_name, data_length + index_length table_size FROM information_schema. TABLES ) t1 ORDER BY table_size DESC LIMIT 10; ################ ###单位M ################
补充7：mysql 主从监控
https://blog.51cto.com/xiaoluoge/2476375
基于prometheus构建mysql可视化监控平台
