本文将带您了解关于mysql小白系列_05日常操作的新内容，同时我们还将为您解释mysql小白教程的相关知识，另外，我们还将为您提供关于Can''tconnecttolocalMySQLservert
本文将带您了解关于mysql 小白系列_05 日常操作的新内容，同时我们还将为您解释mysql小白教程的相关知识，另外，我们还将为您提供关于Can''t connect to local MySQL server through socket ''/opt/lampp/var/mysql/mysql.sock'' (2)、Can''t connect to local MySQL server through socket ''/var/lib/mysql/mysql.sock''、CentOS yum安装mysql后 Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’、CentOS7 日常操作的实用信息。
本文目录一览：- mysql 小白系列_05 日常操作（mysql小白教程）
- Can''t connect to local MySQL server through socket ''/opt/lampp/var/mysql/mysql.sock'' (2)
- Can''t connect to local MySQL server through socket ''/var/lib/mysql/mysql.sock''
- CentOS yum安装mysql后 Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’
- CentOS7 日常操作
mysql 小白系列_05 日常操作（mysql小白教程）
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 线上升级
https://dev.mysql.com/doc/refman/5.7/en/upgrading.html
升级前的版本信息
[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)
Can''t connect to local MySQL server through socket ''/opt/lampp/var/mysql/mysql.sock'' (2)
ERROR 2002 (HY000): Can''t connect to local MySQL server through socket ''/opt/lampp/var/mysql/mysql.sock'' (2)
原因：系统盘满了
[root@localhost opt]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
18G 17G 0 100% /
tmpfs 504M 0 504M 0% /dev/shm
/dev/sda1 477M 80M 372M 18% /boot
[root@localhost opt]#
解决：
删除大文件后，重启系统解决
[root@localhost mysql]# /opt/lampp/lampp status
Version: XAMPP for Linux 1.8.3-3
Apache is not running.
MySQL is not running.
ProFTPD is running.
df: 未处理文件系统
[root@localhost opt]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
18G 17G 0 100% /
tmpfs 504M 0 504M 0% /dev/shm
/dev/sda1 477M 80M 372M 18% /boot
[root@localhost opt]#
[root@localhost ~]# /opt/lampp/lampp status
Version: XAMPP for Linux 1.8.3-3
Apache is not running.
MySQL is running.
ProFTPD is running.
转
xampp 无法启动mysql 找不到mysql.sock
(2016-02-24 23:21:24)
转载▼
| 分类： 技术 |
如果xampp中的mysql启动不了，出现ERROR 2002 (HY000): Can''t connect to local MySQL server through socket ''/opt/lampp/var/mysql/mysql.sock'' (2)报错，
停止xampp的时候报：
-bash-4.1# /opt/lampp/lampp stop
Stopping XAMPP for Linux 1.8.2-6...
XAMPP: Stopping Apache...ok.
XAMPP: Stopping MySQL...ok.
XAMPP: Stopping ProFTPD...kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
fail.
解决办法：
如果网上一些方法不好用的话，可以试试以下方法：
1. 确定系统盘是否满了
#df -h
2. 删除/opt/lampp目录中的pid文件（删掉后xampp重启时会重建，如果不放心，可以先备份lampp目录）
删除mysql相关缓存：
#rm -rf /opt/lampp/var/mysql/VM_*
删除proftp相关缓存：
#rm -rf /opt/lampp/var/proftpd.pid
如果找不到pid文件，可以搜一下：
#find /opt/lampp -name ''*.pid''
Can''t connect to local MySQL server through socket ''/var/lib/mysql/mysql.sock''
MySQL已经被我移到数据盘了，本地连接数据库会报错：Can''t connect to local MySQL server through socket ''/var/lib/mysql/mysql.sock''
但是远程是可以连接的，my.cnf设置mysql的根目录也改成了数据盘的地址，还要在加上client的参数，设置如下：
[client]
socket = /home/data/mysql/mysql.sock
之后重启下mysql就可以了
CentOS yum安装mysql后 Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’
亲，是不是忘记了开MysqL服务，service MysqLd start
CentOS7 日常操作
A 安装netstat
1.首先配置好本机的yum源: yum repolist all
2.利用netstat命令，却提示：-bash: netstat: command not found
3.执行yum provides */netstat命令就可以看到提供命令的工具包net-tools-2.0-0.17.20131004git.el7.x86_64
4.安装工具包：yum install net-tools
5.利用本地服务测试：netstat
netstat -a
查看所有端口
netstat -tunlp
查看端口占用情况
netstat -tunlp | grep 端口号
查看某端口占用情况
B 防火墙
CentOS7在防火墙与端口上的操作
CentOS7使用systemctl指令来管理系统的单一服务，在CentOS7中对于firewalld（防火墙）服务的开启、关闭、状态查询也同样是使用该指令，操作如下：
启动防火墙： systemctl start firewalld
查看防火墙状态： systemctl status firewalld
关闭防火墙： systemctl stop firewalld
开机时启用防火墙服务：systemctl enable firewalld
开机时禁用防火墙服务：systemctl disable firewalld
查询防火墙服务是否开机启动：systemctl is-enabled firewalld
查询已经启动的服务列表：systemctl list-unit-files|grep enabled
查询启动失败的服务列表：systemctl --failed
在安装软件或列库时，除了直接开启和关闭防火墙，也可以通过对端口的操作直接开放连接；添加端口：firewall-cmd --zone=public --add-port=80/tcp --permanent
更新防火墙规则：firewall-cmd --reload
查看端口状态：firewall-cmd --zone=public --query-port=80/tcp
删除开放的端口：firewall-cmd --zone=public --remove-port=80/tcp --permanent
每次都更新防火墙规则，都需要重新更新：firewall-cmd --reload
在更新完防火墙的设置后，也可以查看所有开启的端口：firewall-cmd --zone=public --list-ports
C 安装zip、unzip应用。
yum install zip unzip压缩和解压文件
以下命令均在/home目录下操作
cd /home #进入/home目录
a、把/home目录下面的mydata目录压缩为mydata.zip
zip -r mydata.zip mydata #压缩mydata目录
b、把/home目录下面的mydata.zip解压到mydatabak目录里面
unzip mydata.zip -d mydatabak
c、把/home目录下面的abc文件夹和123.txt压缩成为abc123.zip
zip -r abc123.zip abc 123.txt
d、把/home目录下面的wwwroot.zip直接解压到/home目录里面
unzip wwwroot.zip
e、把/home目录下面的abc12.zip、abc23.zip、abc34.zip同时解压到/home目录里面
unzip abc\*.zip
f、查看把/home目录下面的wwwroot.zip里面的内容
unzip -v wwwroot.zip
g、验证/home目录下面的wwwroot.zip是否完整
unzip -t wwwroot.zip
h、把/home目录下面wwwroot.zip里面的所有文件解压到第一级目录
unzip -j wwwroot.zip
D 解压和压缩
linux命令中tar后跟的zxvf是什么意思
分别是四个参数:x:从 tar 包中把文件提取出来z:表示 tar 包是被 gzip 压缩过的，所以解压时需要用 gunzip 解压v:显示详细信息
fxxx.tar.gz:指定被处理的文件是 xxx.tar.gz
这些都是tar的参数。.tar.gz是一个压缩包，.tar只是打包而没有压缩，注意这点。
1. z：通过gzip支持压缩或解压缩。还有其他的压缩或解压缩方式，比如j表示bzip2的方式。
2. x：解压缩。c是压缩。
3. v:在压缩或解压缩过程中显示正在处理的文件名。
4. f：f后面必须跟上要处理的文件名。也就是说你不能写成这样tar-zxfv zhcon-0.2.5.tar.gz
解压
tar解压
tar xvf filename.tar #将filename.tar解压到当前文件夹，保留原文件
gz解压
gunzip filename.gz #不保留原文件
gunzip -c filename.gz > filename #保留原文件
tar.gz / tgz解压
tar -zxvf filename.tar.gz #解压到当前目录，保留原文件
tar -zxvf filename.tar.gz -C dir #解压到dir目录，保留原文件
bz2解压
bunzip2 filename.bz2
tar.bz2 解压
tar jxvf filename.tar.bz2
压缩
tar 压缩
tar -cvf filename.tar dir #将目录dir中压缩到filename.tar中，保留原文件
gz压缩
gzip filename #不保留原文件
gzip -c filename > filename.gz #保留原文件
tar.gz / tgz压缩
tar zcvf filename.tgz dir #将dir目录压缩到filename.tgz，dir也可以是文件名
bz2 压缩
bzip2 -z filename
tar.bz2压缩
tar jcvf filename.tar.bz2 dir #dir为目录或文件名
E
安装命令：
yum install -y lrzsz使用rz上传文件到服务器上：
rz
今天关于mysql 小白系列_05 日常操作和mysql小白教程的分享就到这里，希望大家有所收获，若想了解更多关于Can''t connect to local MySQL server through socket ''/opt/lampp/var/mysql/mysql.sock'' (2)、Can''t connect to local MySQL server through socket ''/var/lib/mysql/mysql.sock''、CentOS yum安装mysql后 Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’、CentOS7 日常操作等相关知识，可以在本站进行查询。
本文标签：
