mysql小白系列_05 日常操作
mysql启动/关闭
- my.cnf的调用顺序
[root@docker02 bin]# ./mysql --help
Default options are read from the following files in the given order:
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf
- 推荐启动
./mysqld_safe --defaults-file=/data/my3306/my.cnf --user=mysql &
- 其他启动/关闭
#SUSE启动
service mysql start/stop/status
mysql start/stop/status
/etc/init.d/mysql start/stop/status
#centos/redhat
service mysqld start
mysqld.server start/stop/status
/etc/init.d/mysqld start/stop/status
实际启动过程： mysql.server -> mysqld_safe -> mysqld
- 指定参数文件启动
mysqld --defaults-file=/data/my3307/my.cnf --user=mysql &
- 多实例启动/关闭/状态
#启动
mysqld_multi --defaults-extra-file=/data/my3306/my.cnf start 1,2 &
#关闭
mysqld_multi --defaults-extra-file=/data/my3306/my.cnf stop 1,2
#查看状态
mysqld_multi --defaults-extra-file=/data/my3306/my.cnf report
centos7 systemctl status mysqld (service mysqld status)
/etc/systemd/system/mysql.service 软连接到/usr/lib/systemd/system/mysqld.service
mysqld.service也是调用mysqld_safe(ExecStart=/usr/bin/mysqld_safe --basedir=/usr)
- 通过socket关闭
./mysqladmin -S /data/my3307/run/mysql.sock shutdown
mysql登录
- 默认mysql
mysql如果不加root，以当前OS用户作为登录用户连接本地3306端口实例
- 本地指定用户密码登录
mysql -u$username -p$password
- 远程标准端口3306登录
mysql -u$username -p$password -h$ip
- 远程非标准端口3306登录
mysql -u$username -p$password -h$ip -P$port
- 使用socket登录
mysql -uroot -S /data/my3307/run/mysql.sock
账户权限设置
创建用户
- create仅创建用户不授权
create user 'yzw' identified by 'yzw';
- grant创建用户并授权ALL
grant all privileges on *.* to 'yzw1'@'%' identified by 'yzw1' with grant option;
- insert into user
#mysql> insert into mysql.user (host,user,password) values ('127.0.0.1','yzw2',password('yzw2'));
#ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value
#原因： sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
insert into mysql.user (host,user,password,ssl_cipher,x509_issuer,x509_subject) values ('127.0.0.1','yzw2',password('yzw2'),'','','');
授权
- 增删改查表
grant select,insert,update,delete on db1.* to 'yzw'@'%';
- 操作外键表
grant references on db1.* to 'yzw'@'%';
- 操作临时表
grant create temporary tables on db1.* to 'yzw'@'%';
- 操作索引
grant index on db1.* to 'yzw'@'%';
- 操作视图
grant create,show view on db1.* to 'yzw'@'%';
- 创建、查看存储过程
grant create routine on db1.* to 'yzw'@'%';
- 更改、删除存储过程
grant alter routine on db1.* to 'yzw'@'%';
- 执行存储过程
grant execute on db1.* to 'yzw'@'%';
- 查询所有库所有表
grant select on *.* to 'yzw'@'%';
- 全授权
grant all privileges on *.* to 'yzw'@'%';
- 单表授权
grant select,insert,update,delete on db1.t3 to 'yzw'@'%';
- 表的列上授权
grant select(id,name1) on db1.t3 to 'yzw'@'%';
- 存储过程函数授权
grant execute on procedure db1.proce_t3 to 'yzw'@'%';
grant execute on function db1.func_t3 to 'yzw'@'%';
查看权限
- 查看自己的权限
show grants;
- 查看其它用户的权限
show grants for 'yzw1'@'%';
撤销授权
revoke ... from ...
mysql> show grants for 'yzw1'@'%';
+--------------------------------------------------------------------------------------------------------------------------------+
| Grants for yzw1@% |
+--------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'yzw1'@'%' IDENTIFIED BY PASSWORD '*6D3C985F10B257A0C63744181EC491CB468CE8A8' WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> revoke all on *.* from 'yzw1'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'yzw1'@'%';
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for yzw1@% |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'yzw1'@'%' IDENTIFIED BY PASSWORD '*6D3C985F10B257A0C63744181EC491CB468CE8A8' WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
直接更改表user后需要重新登新权限才生效，否则需要flush privilege到内存
mysql数据库安全配置
六原则
- 禁用多余的管理员账号
- 删除db表的数据
- 删除test库
- 修改管理员账户名
- 修改root弱密码
- 权限最小化
表删除操作
任何删除动作都需要备份
- 查看需要删除的表
show tables;
- 查看是否有进程在访问表
show processlist;
- 重命名表
rename table t3 to t3_bak;
- 导出表
mysqldump -h127.0.0.1 -uroot t3_bak > /tmp/t3_bak.sql
- 删表
drop table t3_bak;
- 检查删除结果
show tables from d3 like '%t3%';
在线迁移mysql
1.确认工作
- 主库server_id log_bing
mysql> show variables like '%server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 3306 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------+
| log_bin | ON |
| log_bin_basename | /data/my3306/log/binlog/binlog |
| log_bin_index | /data/my3306/log/binlog/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------------------------+
6 rows in set (0.00 sec)
- 从库server_id log_bing
mysql> show variables like '%server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 3307 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------+
| log_bin | ON |
| log_bin_basename | /data/my3307/log/binlog/binlog |
| log_bin_index | /data/my3307/log/binlog/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------------------------+
6 rows in set (0.00 sec)
2.主库创建复制账号
GRANT REPLICATION SLAVE ON *.* to 'repuser'@'172.16.2.154' identified by 'repuser';
远程登录：mysql -urepuser -prepuser -P3306 -h172.16.2.154
3.主库执行在线全备
先插入几条数据
mysql> create database db1 character set utf8;
Query OK, 1 row affected (0.00 sec)
mysql> create table t1(id int,name1 varchar(10),name2 varchar(10));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t1 values (1,'yzw1','yzw11');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values (2,'yzw2','yzw12');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t1;
+------+-------+-------+
| id | name1 | name2 |
+------+-------+-------+
| 1 | yzw1 | yzw11 |
| 2 | yzw2 | yzw22 |
+------+-------+-------+
2 rows in set (0.00 sec)
4.备份命令
备份用户授权
create user xtrabackup@'localhost' identified by 'xtrabackup';
grant reload,lock tables,replication client,create tablespace,process,super on *.* to xtrabackup@'localhost' ;
grant create,insert,select on percona_schema.* to xtrabackup@'localhost' ;
全备
innobackupex --defaults-file=/data/my3306/my.cnf --user=xtrabackup --password='xtrabackup' --port=3306 --host=localhost --socket=/data/my3306/run/mysql.sock --defaults-group=mysqld1 /backup
innobackupex: Error: mysql child process has died: mysql: unknown variable 'pid_file=/data/my3306/run/mysqld.pid'
注释[mysql]的pid_file=/data/my3306/run/mysqld.pid
2018-02-13 16:02:40 7fd7190e0740 InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
2018-02-13 16:02:40 7fd7190e0740 InnoDB: File name ./ib_logfile0
2018-02-13 16:02:40 7fd7190e0740 InnoDB: File operation call: 'open' returned OS error 71.
2018-02-13 16:02:40 7fd7190e0740 InnoDB: Cannot continue operation.
innobackupex: Error: ibbackup child process has died at /usr/bin/innobackupex line 386.
[root@docker02 2018-02-13_16-02-38]# xtrabackup --defaults-file=/data/my3306/my.cnf --print-param
# This MySQL options file was generated by XtraBackup.
[mysqld]
datadir = "./"
tmpdir = "/tmp"
innodb_data_home_dir = "./"
innodb_data_file_path = "ibdata1:10M:autoextend"
innodb_log_group_home_dir = "./"
innodb_log_files_in_group = 2
innodb_log_file_size = 5242880
innodb_flush_method = ""
innodb_page_size = 16384
innodb_fast_checksum = 0
innodb_log_block_size = 512
innodb_buffer_pool_filename = "ib_buffer_pool"
目录问题 https://www.percona.com/forums/questions-discussions/percona-xtrabackup/13396-file-name-ib_logfile0-innodb-file-operation-call-open-returned-os-error-71
因为一个my.cnf包含多个实例，需要告诉xtrabackup备份的是哪个实例--default-group=mysqld1
5.主库环境拷贝备份到从库环境
略
6.从库环境执行全量恢复
停止从库上的实例，清空数据data和日志log目录
- apply log
innobackupex --defaults-file=/data/my3306/my.cnf --apply-log --user=xtrabackup --password='xtrabackup' --port=3306 --host=localhost --socket=/data/my3306/run/mysql.sock --defaults-group=mysqld1 /backup/2018-02-13_16-28-56
- copy-back
innobackupex --defaults-file=/data/my3306/my.cnf --copy-back --user=xtrabackup --password='xtrabackup' --port=3307 --host=localhost --socket=/data/my3307/run/mysql.sock --defaults-group=mysqld2 /backup/2018-02-13_16-28-56
更改授权 chown -R mysql:mysql /data/my3307
启动数据库 mysqld_multi --defaults-extra-file=/data/my3306/my.cnf start 2
7.确认从库是从哪个binlog的哪个positiion开始的
[root@docker02 2018-02-13_16-28-56]# cat xtrabackup_binlog_info
binlog.000009 790
8.从库环境设置主从复制
CHANGE MASTER TO
MASTER_HOST='172.16.2.154',
MASTER_USER='repuser',
MASTER_PASSWORD='repuser',
MASTER_LOG_FILE='binlog.000009',
MASTER_LOG_POS=790;
[root@docker02 run]# mysql -uroot -h127.0.0.1 -P3307 --socket=/data/my3307/run/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.39-log Source distribution
Copyright (c) 2000, 2018, 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> CHANGE MASTER TO
-> MASTER_HOST='172.16.2.154',
-> MASTER_USER='repuser',
-> MASTER_PASSWORD='repuser',
-> MASTER_LOG_FILE='binlog.000009',
-> MASTER_LOG_POS=790;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql>
9.从库环境执行start slave启动复制
start slave;
查看状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.2.154
Master_User: repuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000009
Read_Master_Log_Pos: 790
Relay_Log_File: relaylog.000002
Relay_Log_Pos: 280
Relay_Master_Log_File: binlog.000009
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 790
Relay_Log_Space: 446
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3306
Master_UUID: dee097da-0bac-11e8-a9a8-005056a37249
Master_Info_File: /data/my3307/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
10.查看从库上的数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.03 sec)
mysql> select * from db1;
ERROR 1046 (3D000): No database selected
mysql> use db1
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> select * from t1;
+------+-------+-------+
| id | name1 | name2 |
+------+-------+-------+
| 1 | yzw1 | yzw11 |
| 2 | yzw2 | yzw22 |
+------+-------+-------+
2 rows in set (0.02 sec)
11.主库继续写数据进来
mysql> insert into t1 values (3,'yzw3','yzw33');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values (4,'yzw4','yzw44');
Query OK, 1 row affected (0.00 sec)
12.从库已经可以马上看到数据了
mysql> select * from t1;
+------+-------+-------+
| id | name1 | name2 |
+------+-------+-------+
| 1 | yzw1 | yzw11 |
| 2 | yzw2 | yzw22 |
+------+-------+-------+
2 rows in set (0.02 sec)
mysql> select * from t1;
+------+-------+-------+
| id | name1 | name2 |
+------+-------+-------+
| 1 | yzw1 | yzw11 |
| 2 | yzw2 | yzw22 |
| 3 | yzw3 | yzw33 |
| 4 | yzw4 | yzw44 |
+------+-------+-------+
4 rows in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 3307 |
+---------------+-------+
1 row in set (0.00 sec)
13.主库环境设为read only
mysql> show variables like '%read_only%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| innodb_read_only | OFF |
| read_only | OFF |
| tx_read_only | OFF |
+------------------+-------+
3 rows in set (0.01 sec)
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
mysql> set global read_only=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like '%read_only%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| innodb_read_only | OFF |
| read_only | ON |
| tx_read_only | OFF |
+------------------+-------+
3 rows in set (0.00 sec)
14.从库追主库binlog日志完毕，开启新主库
mysql线上升级
升级前的版本信息
[root@mysql01 my3306]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.39-log Source distribution
Copyright (c) 2000, 2018, 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> show variables like '%server_id%';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| server_id | 3306 |
| server_id_bits | 32 |
+----------------+-------+
2 rows in set (0.00 sec)
mysql> select version();
+------------+
| version() |
+------------+
| 5.6.39-log |
+------------+
1 row in set (0.00 sec)
下载二进制更新包
wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.21.tar.gz
升级前备份
inplace更新文件
- 原来的安装
cmake \
-DCMAKE_INSTALL_PREFIX=/data/my3306 \
-DINSTALL_DATADIR=/data/my3306/data \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=all \
-DWITH_SSL=yes \
-DWITH_EMBEDDED_SERVER=1 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DMYSQL_UNIX_ADDR=/data/my3306/run/mysql.sock \
-DMYSQL_TCP_PORT=3306 \
-DENABLED_LOCAL_INFILE=1 \
-DSYSCONFDIR=/etc \
-DWITH_READLINE=on
- 解压5.7源码包
tar -zxvf mysql-5.7.21.tar.gz && cd mysql-5.7.21
- cmake
报错
-- MySQL 5.7.21
-- Packaging as: mysql-5.7.21-Linux-x86_64
-- Looked for boost/version.hpp in and
-- BOOST_INCLUDE_DIR BOOST_INCLUDE_DIR-NOTFOUND
-- LOCAL_BOOST_DIR
-- LOCAL_BOOST_ZIP
-- Could not find (the correct version of) boost.
-- MySQL currently requires boost_1_59_0
CMake Error at cmake/boost.cmake:81 (MESSAGE):
You can download it with -DDOWNLOAD_BOOST=1 -DWITH_BOOST=<directory>
- 安装boost
wget http://sourceforge.net/projects/boost/files/boost/1.59.0/boost_1_59_0.tar.gz --no-check-certificate
mkdir -p /usr/local/boost
tar -zxvf boost_1_59_0.tar.gz -C /usr/local/boost
- 增加DWITH_BOOST参数
cmake \
-DCMAKE_INSTALL_PREFIX=/data/my3306 \
-DINSTALL_DATADIR=/data/my3306/data \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=all \
-DWITH_SSL=yes \
-DWITH_EMBEDDED_SERVER=1 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DMYSQL_UNIX_ADDR=/data/my3306/run/mysql.sock \
-DMYSQL_TCP_PORT=3306 \
-DENABLED_LOCAL_INFILE=1 \
-DSYSCONFDIR=/etc \
-DWITH_READLINE=on \
-DDOWNLOAD_BOOST=1 \
-DWITH_BOOST=/usr/local/boost
- make && make install
前面目录不标准，可以install到新目录，然后停止数据库，mv/覆盖
开启数据库
mysqld_safe --defaults-file=/data/my3306/my.cnf --user=mysql &
各种error
2018-02-16T16:35:31.025314Z 0 [ERROR] Native table 'performance_schema'.'global_variables' has the wrong structure
2018-02-16T16:35:31.025374Z 0 [ERROR] Native table 'performance_schema'.'session_variables' has the wrong structure
2018-02-16T16:35:31.025625Z 0 [ERROR] Incorrect definition of table mysql.db: expected column 'User' at position 2 to have type char(32), found type char(16).
2018-02-16T16:35:31.025705Z 0 [ERROR] mysql.user has no `Event_priv` column at position 28
2018-02-16T16:35:31.036356Z 0 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
2018-02-16T16:35:31.036589Z 0 [Note] /data/my3306/bin/mysqld: ready for connections.
Version: '5.7.21-log' socket: '/data/my3306/run/mysql.sock' port: 3306 Source distribution
更新数据字典
[root@mysql01 bin]# mysql_upgrade --socket=/data/my3306/run/mysql.sock
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv OK
mysql.db OK
mysql.engine_cost OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.gtid_executed OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.server_cost OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
Upgrading the sys schema.
Checking databases.
db1.t1 OK
sys.sys_config OK
Upgrade process completed successfully.
Checking if update is needed.
[root@mysql01 bin]#
查看升级后的数据库版本
[root@mysql01 log]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.21-log Source distribution
Copyright (c) 2000, 2018, 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> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 3306 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.21-log |
+------------+
1 row in set (0.00 sec)
