# 启动多实例数据库mysqld_multi start # 启动全部配置的多实例mysqld_multi start 3306# 启动指定server-id的实例mysqld_multi start 3306-3307# 启动指定server-id连续的实例# 查看多实例运行状态mysqld_multi reportReporting MySQL serversMySQL server from group: mysqld3306 is runningMySQL server from group: mysqld3307 is running# 关闭多实例数据库mysqld_multi stopmysqld_multi stop 3306mysqld_multi stop 3306-3307
在线迁移MySQL 3306实例上的数据库jfedu到MySQL 3307上。
登陆3306实例，创建jfedu数据库
# 登陆3306实例mysql -S /u01/run/3306/mysql.sock# 查看port和server_idshow variables like 'port';+---------------+-------+| Variable_name | Value |+---------------+-------+| port | 3306 |+---------------+-------+show variables like 'server_id';+---------------+-------+| Variable_name | Value |+---------------+-------+| server_id | 3306 |+---------------+-------+# 创建jfedu数据库，创建表t1并插入数据create database jfedu;use jfedu;create table t1(id int,name varchar(10));insert into t1 values(1,'AAAAA');commit;
在3306实例上创建一个复制帐号
grant replication slave,replication client on *.* to 'repl'@'%' identified by 'repl4Slave';
show variables like 'read_only';+---------------+-------+| Variable_name | Value |+---------------+-------+| read_only | OFF |+---------------+-------+set global read_only=1;
[mysql@mysql ~]$ cd /u01#选择下载的软件包上传[mysql@mysql u01]$ rz[mysql@mysql u01]$ lltotal 698668drwxr-xr-x2 mysql mysql 4096 Feb 2411:16 confdrwxr-xr-x4 mysql mysql 4096 Feb 24 09:45 datadrwxr-xr-x4 mysql mysql 4096 Feb 2411:00logdrwxr-xr-x13 mysql mysql 4096 Feb 2410:16 mysqldrwxr-xr-x35 mysql mysql 4096 Feb 2410:05 mysql-5.6.35-rwxr-xr-x. 1 mysql mysql 32167628 Jan 1711:16 mysql-5.6.35.tar.gz-rw-r--r-- 1 mysql mysql 683233280 Feb 2416:36 mysql-5.7.17-linux-glibc2.5-x86_64.tar # 这个就是二进制mysql5.7软件drwxr-xr-x4 mysql mysql 4096 Feb 24 09:50 rundrwxr-xr-x4 mysql mysql 4096 Feb 24 09:45 tmptar -xpvf mysql-5.7.17-linux-glibc2.5-x86_64.tartar -xpvf mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz# 因为是二进制包，解压后就可以用了，文件夹名改短些mv mysql-5.7.17-linux-glibc2.5-x86_64 mysql5.7# 修改环境变量vi /home/mysql/.bash_profilePATH=$PATH:$HOME/bin:/u01/mysql5.7/bin
查看mysql版本信息
mysql -V--------------mysql Ver 14.14 Distrib 5.7.17, for linux-glibc2.5 (x86_64) using EditLine wrapper--------------mysqld_multi start 3306mysql -S /u01/run/3306/mysql.sockstatus;--------------mysql Ver 14.14 Distrib 5.7.17, for linux-glibc2.5 (x86_64) using EditLine wrapperConnection id: 7Current database:Current user: root@localhostSSL: Not in useCurrent pager: stdoutUsing outfile: ''Using delimiter: ;Server version: 5.6.35-log Source distributionProtocol version: 10Connection: Localhost via UNIX socketServer characterset: utf8Db characterset: utf8Client characterset: utf8Conn. characterset: utf8UNIX socket: /u01/run/3306/mysql.sockUptime: 3 min 57 secThreads: 2 Questions: 16 Slow queries: 0 Opens: 70 Flush tables: 1 Open tables: 63 Queries per second avg: 0.067--------------
升级数据库字典
mysql_upgrade -S /u01/run/3306/mysql.sock--------------Checking if update is needed.Checking server version.Error: Server version (5.6.35-log) does not match with the version ofthe server (5.7.17) with which this program was built/distributed. You canuse --skip-version-check to skip this check.--------------mysql_upgrade -S /u01/run/3306/mysql.sock --skip-version-check--------------Checking if update is needed.Running queries to upgrade MySQL server.mysql_upgrade: [ERROR] 1726: Storage engine 'InnoDB' does not support system tables. [mysql.plugin]--------------# 重启下数据库mysqld_multi stopmysqld --defaults-file=/u01/conf/my3306.cnf &mysql_upgrade -S /u01/run/3306/mysql.sock --skip-version-check--------------Checking if update is needed.Running queries to upgrade MySQL server.Checking system database.mysql.columns_priv OKmysql.db OKmysql.engine_cost OKmysql.event OKmysql.func OKmysql.general_log OKmysql.gtid_executed OKmysql.help_category OKmysql.help_keyword OKmysql.help_relation OKmysql.help_topic OKmysql.innodb_index_stats OKmysql.innodb_table_stats OKmysql.ndb_binlog_index OKmysql.plugin OK......Repairing tablesmysql_old.innodb_index_statsError : Unknown error 1146status : Operation failedmysql_old.innodb_table_statsError : Unknown error 1146status : Operation failedmysql_old.slave_master_infoError : Unknown error 1146status : Operation failedmysql_old.slave_relay_log_infoError : Unknown error 1146status : Operation failedmysql_old.slave_worker_infoError : Unknown error 1146status : Operation failedUpgrade process completed successfully.Checking if update is needed.--------------
再查看mysql版本信息，升级完成
mysql -S /u01/run/3306/mysql.sockmysql> status;--------------mysql Ver 14.14 Distrib 5.7.17, for linux-glibc2.5 (x86_64) using EditLine wrapperConnection id: 4Current database:Current user: root@localhostSSL: Not in useCurrent pager: stdoutUsing outfile: ''Using delimiter: ;Server version: 5.7.17-log MySQL Community Server (GPL)Protocol version: 10Connection: Localhost via UNIX socketServer characterset: latin1Db characterset: latin1Client characterset: utf8Conn. characterset: utf8UNIX socket: /u01/run/3306/mysql.sockUptime: 6 min 3 secThreads: 1 Questions: 3174 Slow queries: 0 Opens: 368 Flush tables: 1 Open tables: 25 Queries per second avg: 8.743--------------mysql> select version();+------------+| version() |+------------+| 5.7.17-log |+------------+
select user,host,password from mysql.user;+------+-----------+----------+| user | host | password |+------+-----------+----------+| root | localhost | | # 密码都为空，不安全| root | mysql | || root | 127.0.0.1 | || root | ::1 | || | localhost | || | mysql | |+------+-----------+----------+# insert方式创建用户(用insert方式创建的用户，需要刷新缓存)insert into mysql.user(user,host,password) values('mytt','127.0.0.1',password(123456));flush privileges;select user,host,password from mysql.user;+------+-----------+-------------------------------------------+| user | host | password |+------+-----------+-------------------------------------------+| root | localhost | || root | mysql | || root | 127.0.0.1 | || root | ::1 | || | localhost | || | mysql | || mytt | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |+------+-----------+-------------------------------------------+# mytt登陆mysql命令如下mysql -umytt -p123456 -h127.0.0.1select user();+----------------+| user() |+----------------+| mytt@127.0.0.1 |+----------------+# create方式创建用户create user lyj@'%' identified by '123456'; # %代表可以从任意位置登陆mysql -ulyj -p123456 -h127.0.0.1# 删除用户drop user lyj;
帐户权限设置-用户授权
# 创建新用户并授权grant all privileges on *.* to lyj@'%' identified by '123456';# 查看用户权限show grants for lyj@'%';+-------------------------------------------------------------------------------------------------------------+| Grants for lyj@% |+-------------------------------------------------------------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO 'lyj'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |+-------------------------------------------------------------------------------------------------------------+# 给已有用户授权grant select on *.* to mytt@127.0.0.1;show grants for mytt@127.0.0.1;+--------------------------------------------------------------------------------------------------------------+| Grants for mytt@127.0.0.1 |+--------------------------------------------------------------------------------------------------------------+| GRANT SELECT ON *.* TO 'mytt'@'127.0.0.1' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |+--------------------------------------------------------------------------------------------------------------+
帐户权限设置-权限等级
核心开发权限 select/insert/delete/update
管理权限–表级 create table/drop table/lock table
管理权限–server级别 create database/create user等
MySQL数据库安全配置
1.禁用/删除多余的管理员帐号，设置用户密码
# 删除多余的帐号delete from mysql.user where user !='root'and password='';flush privileges;# 使用set方式设置root用户密码set password for root@localhost = password('123456');set password for root@127.0.0.1 = password('123456');# 使用mysqladmin工具设置密码## 格式: mysqladmin -u用户名 -p旧密码 password 新密码## 密码为空时，直接输入回车确认mysqladmin -uroot -p password 654321Enter password: Warning: Using a password on the command line interface can be insecure.## 修改已有密码mysqladmin -uroot -p654321 password 123456## 执行后会出现如下警告提示，可以忽略：## Warning: Using a password on the command line interface can be insecure.## 翻译过来的意思：在命令行界面上使用密码是不安全的。# 登陆mysqlmysql -uroot -p123456# 删除所有密码为空的帐号delete from mysql.user where password='';flush privileges;# 确保所有用户都有密码select user,host,password from mysql.user;+------+-----------+-------------------------------------------+| user | host | password |+------+-----------+-------------------------------------------+| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 || root | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 || mytt | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |+------+-----------+-------------------------------------------+
2.删除掉db表数据(test权限)
use mysql;truncate table mysql.db;
3.删除test库
drop database test;
4.修改管理员帐户名 5.密码复杂度要求 6.权限最小化
表操作–线上可以直接删除表吗?
生产环境上，不可以直接删除表，要删除表步骤如下：
# 构建环境drop database lyj;create database lyj;use lyj;create table t1 (id int, name varchar(10));insert into t1 values (1,'lyj');# 1.查看表show tables;# 2.检查表是否被访问show processlist;# 3.重命名临时表rename table t1 to t1_bak;# 4.备份临时表mysqldump -uroot -p123456 lyj t1_bak > /tmp/lyj_t1_bak_20170223.sql# 5.一段时间后删除临时表drop table t1_bak;show tables from lyj like '%t1%';
常用命令
show databases;use mysql;show tables;select user,host,password from mysql.user;grant all privilege on *.* to test_1@'%';mysql -h127.0.0.1 -utest_1select user();create database jianfeng;create table user(id int,name varchar(10));grant select on jianfeng.user to test_1@'%';flush privileges;show master status\G;change master to xxx;show engine innodb status\Gshow tables from information_schema like 'INNODB%';mysqld_safe --defaults-file=/u01/my3306/my.cnf &mysqladmin -S /u01/my3306/run/mysql.sock shutdown &/u01/mysql/bin/mysql_secure_installation
