centos6.5 安装MYSQL 5.7.11 教程

清除残留
rpm -qa | grep mysql　　// 这个命令就会查看该操作系统上是否已经安装了mysql数据库
有的话，我们就通过 rpm -e 命令 或者 rpm -e --nodeps 命令来卸载掉
rpm -e mysql　　 // 普通删除模式
rpm -e --nodeps mysql　　 // 强力删除模式

从20服务器拷贝文件包到本地服务器
scp sysadm@ip:/usr/local/soft/mysql-5.7.11-Linux-glibc2.5-x86_64.tar.gz /usr/local/soft/

首先MySQL依赖于库libaio，所以我们就先安装这个库，具体命令如下
yum search libaio // search for info
yum install libaio // install library

groupadd mysql // 添加用户组
useradd -r -g mysql -s /bin/false mysql // 添加mysql用户
cd /usr/local/soft // 最好是安装在这个目录下，如果不是可能会报错
tar -zxf mysql-5.7.11-linux-glibc2.5-x86_64.tar.gz …/mysql

[root@CXGLCon-7 local]# cd mysql/
[root@CXGLCon-7 mysql]# mkdir mysql-files
[root@CXGLCon-7 mysql]# chmod 750 mysql-files/
// .此时代表的文件夹是/usr/local/mysql, 改当前文件夹及子目录的所有者
[root@CXGLCon-7 mysql]# chown -R mysql .
// .此时代表的文件夹是/usr/local/mysql, 改当前文件夹及子目录的用户组
[root@CXGLCon-7 mysql]# chgrp -R mysql .
// Before MySQL 5.7.6, 由于我们安装的是5.7.11，所以这句我们不执行
bin/mysql_install_db --user=mysql
// MySQL 5.7.6 and up, 这句好像是正式安装了，它会给你一个root的初始密码，最好要记住，记不住的话也行后面也有解决办法
[root@CXGLCon-7 mysql]# bin/mysqld --initialize --user=mysql
2018-07-09T11:17:12.934040Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-07-09T11:17:13.915961Z 0 [Warning] InnoDB: New log files created, LSN=45790
2018-07-09T11:17:14.064279Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-07-09T11:17:14.134342Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: a1a1d7d6-8369-11e8-b902-0050568b3b8a.
2018-07-09T11:17:14.138351Z 0 [Warning] Gtid table is not ready to be used. Table ‘mysql.gtid_executed’ cannot be opened.
2018-07-09T11:17:14.140367Z 1 [Note] A temporary password is generated for root@localhost: :=Wl>JI%V0OH

NqqMeT/Zp6yW

// MySQL 5.7.6 and up UqiCyXaFW4.E
[root@CXGLCon-7 mysql]# bin/mysql_ssl_rsa_setup
Generating a 2048 bit RSA private key
…+++
…+++
writing new private key to ‘ca-key.pem’

Generating a 2048 bit RSA private key
…+++
…+++
writing new private key to ‘server-key.pem’

Generating a 2048 bit RSA private key
…+++
…+++
writing new private key to ‘client-key.pem’

[root@CXGLCon-7 mysql]# chown -R root .
[root@CXGLCon-7 mysql]# chown -R mysql data mysql-files
[root@CXGLCon-7 mysql]# bin/mysqld_safe --user=mysql &
[1] 12534
[root@CXGLCon-7 mysql]# 2018-07-09T11:19:41.312456Z mysqld_safe Logging to ‘/usr/local/mysql/data/CXGLCon-7.err’.
2018-07-09T11:19:41.375673Z mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

[root@CXGLCon-7 mysql]# cd data/

[root@CXGLCon-7 mysql]# pwd
/usr/local/mysql

[root@CXGLCon-7 mysql]# cd support-files/
[root@CXGLCon-7 support-files]# ll
total 28
-rw-r–r-- 1 root mysql 773 Feb 2 2016 magic
-rw-r–r-- 1 root mysql 1126 Feb 2 2016 my-default.cnf
-rwxr-xr-x 1 root mysql 1061 Feb 2 2016 mysqld_multi.server
-rwxr-xr-x 1 root mysql 894 Feb 2 2016 mysql-log-rotate
-rwxr-xr-x 1 root mysql 10975 Feb 2 2016 mysql.server
[root@CXGLCon-7 support-files]# vi mysql.server

[root@CXGLCon-7 support-files]# cp mysql.server /etc/init.d/mysqld
[root@CXGLCon-7 support-files]# ll
total 28
-rw-r–r-- 1 root mysql 773 Feb 2 2016 magic
-rw-r–r-- 1 root mysql 1126 Feb 2 2016 my-default.cnf
-rwxr-xr-x 1 root mysql 1061 Feb 2 2016 mysqld_multi.server
-rwxr-xr-x 1 root mysql 894 Feb 2 2016 mysql-log-rotate
-rwxr-xr-x 1 root mysql 10975 Feb 2 2016 mysql.server
[root@CXGLCon-7 support-files]#
[root@CXGLCon-7 support-files]#
[root@CXGLCon-7 support-files]# vi my-default.cnf

添加信息：
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306

[root@CXGLCon-7 support-files]# cp my-default.cnf my.cnf
[root@CXGLCon-7 support-files]#
[root@CXGLCon-7 support-files]#
[root@CXGLCon-7 support-files]# cp my-default.cnf my-default-bak.cnf

[root@CXGLCon-7 support-files]# cp my-default.cnf /etc/my.cnf
[root@CXGLCon-7 support-files]# vi /etc/profile

MYSQL

export MYSQL_HOME=/usr/local/mysql
export PATH=$PATH:$MYSQL_HOME/bin

[root@CXGLCon-7 support-files]# source /etc/profile
[root@CXGLCon-7 support-files]# mysql -uroot -p
Enter password: :=Wl>JI%V0OH
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.11

Copyright © 2000, 2016, Oracle and/or its affiliates. All rights reserved.

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>

修改root密码：

You must reset your password using ALTER USER statement before executing this statement.
安装完mysql 之后，登陆以后，不管运行任何命令，总是提示这个
step 1: SET PASSWORD = PASSWORD(‘bbqHCP%1Cloud’);
step 2: ALTER USER ‘root’@‘localhost’ PASSWORD EXPIRE NEVER;
step 3: flush privileges;

[root@CP-E5CX-P support-files]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.11

mysql> use mysql;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> SET PASSWORD = PASSWORD(‘bbqHCP%1Cloud’);
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> ALTER USER ‘root’@‘localhost’ PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> use mysql;
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> CREATE USER root@’%’ IDENTIFIED BY ‘bbqHCP%1Cloud’;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON . TO root@’%’ ;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

[root@CXGLCon-7 init.d]# ./mysqld restart
Shutting down MySQL…2018-07-09T12:16:21.605064Z mysqld_safe mysqld from pid file /usr/local/mysql/data/CXGLCon-7.pid ended[ OK ]
Starting MySQL. [ OK ]
[1]+ Done bin/mysqld_safe --user=mysql (wd: /usr/local/mysql)
(wd now: /etc/init.d)
[root@CXGLCon-7 init.d]# chkconfig mysqld on
[root@CXGLCon-7 init.d]# chmod 755 /etc/rc.d/init.d/mysqld
[root@CXGLCon-7 init.d]# service mysqld restart
Shutting down MySQL… [ OK ]
Starting MySQL. [ OK ]

https://www.cnblogs.com/tuhooo/p/5189236.html

MySQL自动备份

对于mysqldump 要如何避免出现(Warning:Using a password on the command line interface can be insecure.) 警告信息呢?
vim /etc/mysql/my.cnf
[mysqldump]
user=your_backup_user_name
password=your_backup_password

数据库自动备份步骤：

1.修改 vi /etc/my.cnf

添加

[mysqldump]
user=用户名
password=密码

2.写一个shell脚本备份数据库

if [ ! -d $(date+mkdir/usr/local/mysqlbackup/bakdata/$(date +%Y%m%d);
fi
/usr/local/mysql/bin/mysqldump g_projmgrdb > /usr/local/mysqlbackup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump g_cgmgrdb > /usr/local/mysqlbackup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump g_commondb > /usr/local/mysqlbackup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump g_commudb > /usr/local/mysqlbackup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump g_qyggdb > /usr/local/mysqlbackup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump gp_boss > /usr/local/mysqlbackup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump gp_ucdb > /usr/local/mysqlbackup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump p_autotestdb > /usr/local/mysqlbackup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump p_checkdb > /usr/local/mysqlbackup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump p_devplatdb > /usr/local/mysqlbackup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump p_khsysdb > /usr/local/mysqlbackup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump p_reviewdb > /usr/local/mysqlbackup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
#删除7天前备份的文件
find /usr/local/mysqlbackup/bakdata/ -mtime +10 -name “*” -exec rm -rf {} ;
~

3.创建定时任务执行脚本：每天23点59秒定时执行备份
crontab -e
59 23 * * * /usr/local/mysqlbackup/bin/mysqlbak.sh

4.#删除10天前备份的文件
find /usr/local/mysqlbackup/bakdata/ -mtime +10 -name “*” -exec rm -rf {} ;

mysql 自动备份并且自动备份到其他服务器

->1、首先有两台服务器A和B，需要设置 A和B免密登陆 设置普通用户sysadm 免密登录

在A上： 用普通用户sysadm执行命令 ssh-keygen
/home/sysadm/.ssh 目录下会生成公钥和私钥,将公钥 id_rsa.pub 拷贝至B服务器
/home/sysadm/.ssh 下并命名为 authorized_keys

如果希望ssh公钥生效需满足至少下面两个条件：

1. .ssh目录的权限必须是700
2. .ssh/authorized_keys文件权限必须是600

ssh sysadm@ip 即可实现免密登录

centos7 设置免密登录很简单 ：
A B 两台服务器
切换到免密账号 sysadm 在两台服务器上同时执行下面两条命令 A上执行两条 B上也两条
ssh-copy-id A
ssh-copy-id B
这样就能A B 就能用sysadm 账号相互免密登录了

-> 2、设置定时任务由sysadm 这个用户执行

1. 创建任务（普通用户）
crontab -e 编辑计划任务

2. 给普通用户授权sudo（root用户）
给普通用户授权sudo，用sudo 提升普通用户service命令权限
1）切换root用户，可以用vim /etc/sudoers对文件编辑(先赋权限chmod u+w /etc/sudoers
)，但最好使用visudo命令编辑，原因两点：一是它能够防止两个用户同时
修改它；二是它也能进行有限的语法检查。
2）找到"root ALL=(ALL) ALL"，在下面加一行“XXX ALL=(ALL) ALL”即完成授权 
“XXX”是用户名。
3）此时该用户拥有root所有权限，要做一下限制，先说一下上面三个ALL的含义：
①网络中的主机名（建议用ALL，实际不确定远程主机名），
②目标用户，也就是以谁的身份去执行命令（建议ALL）
③命令（最好不用ALL，否则方法二就没意义了，多个命令逗号隔开），默认5分钟sudo密码过期，怕麻烦命令前加“NOPASSWD:”  
所以上面增加内容应改为：
sysadm ALL=(ALL) NOPASSWD:/sbin/service
3. 重新启动任务计划
方法一：    切换到root用户    service crond restart（安全性考虑，不建议此方法）
方法二：    切换普通用户，执行sudo service crond restart即可

4. 在B服务器上创建好备份目录并授权给sysadm账号

A上： chown sysadm:sysadm -R /usr/local/mysqlautobakup/
授权 chomod 777 /usr/local/mysql/bin/mysqldump
B上
mkdir /data/mysqlautobakdata/prod -p
chown sysadm:sysadm -R /data/

在A服务器执行：
scp -r /usr/local/mysqlautobakup/bakdata/$(date +%Y%m%d) sysadm@ip:/data/mysqlautobakdata/prod

mongodump -u gcmsys -p gcmsys --port 28018 --authenticationDatabase gcmsys -d gcmsys -o /usr/local/mongodb-bak/

Linux下设置MySql自动启动

1、将服务文件拷贝到init.d下，并重命名为mysql
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
2、赋予可执行权限
chmod +x /etc/init.d/mysqld
3、添加服务
chkconfig --add mysqld
4、显示服务列表
chkconfig --list
如果看到mysql的服务，并且3,4,5都是on的话则成功，如果是off，则键入
chkconfig --level 345 mysqld on
5、重启电脑
reboot
6、验证：如果看到有监听说明服务启动了
netstat -na | grep 3306

备份

[sysadm@JTCXGL-SV-3 ~]$ more /usr/local/mysqlautobakup/bin/mysqlbak.sh
if [ ! -d $(date+mkdir/usr/local/mysqlautobakup/bakdata/$(date +%Y%m%d);
fi
/usr/local/mysql/bin/mysqldump g_projmgrdb > /usr/local/mysqlautobakup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump g_cgmgrdb > /usr/local/mysqlautobakup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump g_commondb > /usr/local/mysqlautobakup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump g_commudb > /usr/local/mysqlautobakup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump g_qyggdb > /usr/local/mysqlautobakup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump g_reform > /usr/local/mysqlautobakup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump gp_boss > /usr/local/mysqlautobakup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump gp_ucdb > /usr/local/mysqlautobakup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump p_autotestdb > /usr/local/mysqlautobakup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump p_checkdb > /usr/local/mysqlautobakup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump p_devplatdb > /usr/local/mysqlautobakup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump p_khsysdb > /usr/local/mysqlautobakup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump p_reviewdb > /usr/local/mysqlautobakup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump g_cgmgrdb_dev > /usr/local/mysqlautobakup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump g_commondb_dev > /usr/local/mysqlautobakup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump gp_boss_dev > /usr/local/mysqlautobakup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump gp_ucdb_dev > /usr/local/mysqlautobakup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump p_reviewdb_dev > /usr/local/mysqlautobakup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump g_assess > /usr/local/mysqlautobakup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump g_cgmgr_common > /usr/local/mysqlautobakup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump g_demand > /usr/local/mysqlautobakup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump g_knowledge_base > /usr/local/mysqlautobakup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump g_mixedb > /usr/local/mysqlautobakup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump g_specialdb > /usr/local/mysqlautobakup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump new_boss > /usr/local/mysqlautobakup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump new_ucdb > /usr/local/mysqlautobakup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump p_review_common > /usr/local/mysqlautobakup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump plogdb > /usr/local/mysqlautobakup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump pmsdb > /usr/local/mysqlautobakup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump scboss > /usr/local/mysqlautobakup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql
/usr/local/mysql/bin/mysqldump gp_ucdb_new > /usr/local/mysqlautobakup/bakdata/$(date+$(date +%Y%m%d_%H%M%S).sql

#删除30天前备份的文件
find /usr/local/mysqlautobakup/bakdata/ -mtime +30 -name “*” -exec rm -rf {} ;

su sysadm
scp -r /usr/local/mysqlautobakup/bakdata/$(date +%Y%m%d) sysadm@11.11.190.18:/data/mysqlautobakdata/test/