mysql二进制方式部署

http://blog.itpub.net/30126024/viewspace-2221483/

 

1、数据库初始化：

mysql5.6之前
./mysql_install_db --user=root  --datadir=/data/3306/data --basedir=/opt/mysql

mysql5.7之后

mysqld --initialize-insecure --user=root  --datadir=/data/3307/data --basedir=/opt/mysql
mysqld --initialize-insecure --user=root --datadir=/data/3308/data --basedir=/opt/mysql
mysqld --initialize-insecure --user=root --datadir=/data/3309/data --basedir=/opt/mysql

 /root/mysql/mysql57/bin/mysqld --initialize --basedir=/root/mysql/mysql57 --datadir=/root/mysql/mysql57/data --user=root

 

[mysqld]
lower_case_table_names=1
character_set_server=utf8
init_connect='SET NAMES utf8'
skip-name-resolve
max_connections=20000
wait-timeout = 3000
interactive-timeout=3000
log_bin_trust_function_creators=TRUE
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
daemonize = on
user = root
port = 3306
basedir = /root/mysql/mysql57
datadir = /root/mysql/mysql57/data
socket = /tmp/mysql.sock
bind-address = 0.0.0.0
pid-file = /root/mysql/mysql57/tmp/mysqld.pid
character-set-server = utf8
collation-server = utf8_general_ci
max_connections = 2408
log-error = /root/mysql/mysql57/logs/mysqld.log


[client]
character-set-server=utf8
default-character-set=utf8
socket=/tmp/mysql.sock

[mysql]
character-set-server=utf8

2、启动多实例
mysqld_safe --defaults-file=/data/3307/my.cnf & 或mysqld   --defaults-file=/data/3307/my.cnf &
mysqld_safe --defaults-file=/data/3308/my.cnf &
mysqld_safe --defaults-file=/data/3309/my.cnf &

3、测试

netstat -lnp|grep 330

mysql -S /data/3307/mysql.sock
mysql -S /data/3308/mysql.sock
mysql -S /data/3309/mysql.sock

4、 systemd管理多实例

cat >> /etc/systemd/system/mysqld3307.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/opt/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000
EOF
cp /etc/systemd/system/mysqld3307.service /etc/systemd/system/mysqld3308.service
cp /etc/systemd/system/mysqld3307.service /etc/systemd/system/mysqld3309.service
sed -i 's#3307#3308#g' /etc/systemd/system/mysqld3308.service
sed -i 's#3307#3309#g' /etc/systemd/system/mysqld3309.service

systemctl start mysqld3307
systemctl start mysqld3308
systemctl start mysqld3309
netstat -lnp|grep 330
systemctl stop mysqld3309
systemctl stop mysqld3308
systemctl stop mysqld3307
systemctl enable mysqld3307
systemctl enable mysqld3308
systemctl enable mysqld3309

5、忘记密码

mysqladmin -uroot -p password 123

select user,authentication_string,host from mysql.user;

1.停数据库
/etc/init.d/mysqld stop
2.启动数据库为无密码验证模式
mysqld_safe --skip-grant-tables --skip-networking &
update mysql.user set authentication_string=PASSWORD('456') where user='root' and host='localhost';
/etc/init.d/mysqld restart

[root@standby ~]# mysql -uroot -p123
[root@standby ~]# mysql -uroot -p456

 

 

1、rpm -ivh *.rpm
2、service mysql start
3、grep 'temporary password' /var/log/mysql/mysqld.log 查看首次密码
4、mysql -u root -p
5、ALTER USER 'root'@'localhost' IDENTIFIED BY '1qaz2WSX.com'; 修改密码
6、SHOW VARIABLES LIKE 'validate_password%'; 设置密码策略
	关于 mysql 密码策略相关参数；
	1）、validate_password_length  固定密码的总长度；
	2）、validate_password_dictionary_file 指定密码验证的文件路径；
	3）、validate_password_mixed_case_count  整个密码中至少要包含大/小写字母的总个数；
	4）、validate_password_number_count  整个密码中至少要包含阿拉伯数字的个数；
	5）、validate_password_policy 指定密码的强度验证等级，默认为 MEDIUM；
	关于 validate_password_policy 的取值：
	0/LOW：只验证长度；
	1/MEDIUM：验证长度、数字、大小写、特殊字符；
	2/STRONG：验证长度、数字、大小写、特殊字符、字典文件；
	6）、validate_password_special_char_count 整个密码中至少要包含特殊字符的个数；
	eg:set global validate_password_length=4;
	   set global validate_password_policy=LOW;
	   ALTER USER 'root'@'localhost' IDENTIFIED BY '123456aA'; 修改密码
7、密码过期设置
   ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;(指定用户) 
   SET GLOBAL default_password_lifetime = 0;  -- never(全局用户)
8、flush privileges;
9、程访问权限
	GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '1234567890' WITH GRANT OPTION;
10、show variables like '%max_connections%';  查看连接数
11、show variables like '%character%';查看字符
12、update user set authentication_string=password('新密码') where user='root';
13、CREATE DATABASE IF NOT EXISTS wubo DEFAULT CHARSET utf8 COLLATE utf8_general_ci;创建数据库
14、drop database wubo;
15、source path/xxx.sql 导入数据
[mysqld]
default_password_lifetime=0
lower_case_table_names=1
max_connections=20000
character-set-server=utf8
init_connect='SET NAMES utf8'
collation_server = utf8_general_ci
validate_password_policy=0
validate_password_length=4
#validate_password = off
#skip-grant-tables 
sql_mode =STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION