搜索到
3
篇与
的结果
-
MySQL时间戳和时间的获取/相互转换/格式化 获取当前时间戳mysql> select unix_timestamp(now()); +-----------------------+ | unix_timestamp(now()) | +-----------------------+ | 1584524789 | +-----------------------+ 1 row in set (0.00 sec) mysql> select unix_timestamp(); +------------------+ | unix_timestamp() | +------------------+ | 1584524524 | +------------------+ 1 row in set (0.00 sec) 获取当前时间mysql> select now(); +---------------------+ | now() | +---------------------+ | 2020-03-18 17:39:13 | +---------------------+ 1 row in set (0.00 sec) mysql> select date(now()); +-------------+ | date(now()) | +-------------+ | 2020-03-18 | +-------------+ 1 row in set (0.00 sec) 获取三天前的时间mysql> SELECT NOW() - interval 72 hour; +--------------------------+ | NOW() - interval 72 hour | +--------------------------+ | 2020-03-15 17:39:44 | +--------------------------+ 1 row in set (0.00 sec) 时间转时间戳mysql> select unix_timestamp('2018-01-15 09:45:16'); +---------------------------------------+ | unix_timestamp('2018-01-15 09:45:16') | +---------------------------------------+ | 1515980716 | +---------------------------------------+ 1 row in set (0.00 sec) 时间戳转时间mysql> select from_unixtime(1515980716); +---------------------------+ | from_unixtime(1515980716) | +---------------------------+ | 2018-01-15 09:45:16 | +---------------------------+ 1 row in set (0.02 sec) 时间戳格式化mysql> SELECT from_unixtime(1515980716, '%Y-%m-%d %H:%i:%S'); +------------------------------------------------+ | from_unixtime(1515980716, '%Y-%m-%d %H:%i:%S') | +------------------------------------------------+ | 2018-01-15 09:45:16 | +------------------------------------------------+ 1 row in set (0.00 sec) 时间格式化mysql> select date_format(now(), '%Y-%m-%d'); +--------------------------------+ | date_format(now(), '%Y-%m-%d') | +--------------------------------+ | 2020-03-18 | +--------------------------------+ 1 row in set (0.00 sec) mysql> select date_format('2018-01-15 09:45:16', '%Y-%m-%d'); +------------------------------------------------+ | date_format('2018-01-15 09:45:16', '%Y-%m-%d') | +------------------------------------------------+ | 2018-01-15 | +------------------------------------------------+ 1 row in set (0.00 sec) 可视化转换 http://tool.chinaz.com/Tools/unixtime.aspx 函数：FROM_UNIXTIME 作用：将MYSQL中以INT(11)存储的时间以"YYYY-MM-DD"格式来显示。 语法：FROM_UNIXTIME(unix_timestamp,format)返回表示 Unix 时间标记的一个字符串，根据format字符串格式化。format可以包含与DATE_FORMAT()函数列出的条目同样的修饰符。根据format字符串格式化date值。 下列修饰符可以被用在format字符串中：%M 月名字(January……December) %W 星期名字(Sunday……Saturday) %D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。） %Y 年, 数字, 4 位 %y 年, 数字, 2 位 %a 缩写的星期名字(Sun……Sat) %d 月份中的天数, 数字(00……31) %e 月份中的天数, 数字(0……31) %m 月, 数字(01……12) %c 月, 数字(1……12) %b 缩写的月份名字(Jan……Dec) %j 一年中的天数(001……366) %H 小时(00……23) %k 小时(0……23) %h 小时(01……12) %I 小时(01……12) %l 小时(1……12) %i 分钟, 数字(00……59) %r 时间,12 小时(hh:mm:ss [AP]M) %T 时间,24 小时(hh:mm:ss) %S 秒(00……59) %s 秒(00……59) %p AM或PM %w 一个星期中的天数(0=Sunday ……6=Saturday ） %U 星期(0……52), 这里星期天是星期的第一天 %u 星期(0……52), 这里星期一是星期的第一天 %% 一个文字“%”。例子：SELECT FROM_UNIXTIME(1234567890, ‘%Y-%m-%d %H:%i:%S’) SELECT *,FROM_UNIXTIME(created, ‘%Y-%m-%d’) as riqi FROM rc_ms_users -
在CentOS 8上安装MySQL 8 一、概述基于CentOS8.3mini 安装Mysql 8二、开始安装 查看mysql模块# dnf whatprovides mysql mysql-8.0.13-1.module_el8.0.0+41+ca30bab6.x86_64 : MySQL client programs and shared libraries Repo : AppStream Matched from: Provide : mysql = 8.0.13-1.module_el8.0.0+41+ca30bab6 因此，只需运行以下命令即可轻松安装MySQL 8服务器；dnf install mysql-server 同时安装MySQL 8服务器和客户端；dnf install @mysql ======================================================================================================================================================= Package Arch Version Repository Size ======================================================================================================================================================= Installing group/module packages: mysql-server x86_64 8.0.13-1.module_el8.0.0+41+ca30bab6 AppStream 25 M Installing dependencies: mariadb-connector-c-config noarch 3.0.7-1.el8 AppStream 13 k mecab x86_64 0.996-1.module_el8.0.0+41+ca30bab6.9 AppStream 397 k mysql x86_64 8.0.13-1.module_el8.0.0+41+ca30bab6 AppStream 9.2 M mysql-common x86_64 8.0.13-1.module_el8.0.0+41+ca30bab6 AppStream 142 k mysql-errmsg x86_64 8.0.13-1.module_el8.0.0+41+ca30bab6 AppStream 523 k protobuf-lite x86_64 3.5.0-7.el8 AppStream 150 k Installing module profiles: mysql/server Enabling module streams: mysql 8.0 Transaction Summary ======================================================================================================================================================= Install 7 Packages Total download size: 36 M Installed size: 243 M Is this ok [y/N]: y 在CentOS 8上运行MySQL 8 安装完成后，启动并启用MySQL 8服务器以在系统启动时运行。# systemctl start mysqld # systemctl enable mysqld 检查MySQL 8服务器的状态；systemctl status mysqld ● mysqld.service - MySQL 8.0 database server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: active (running) since Wed 2019-10-02 13:19:32 EDT; 42s ago Main PID: 29142 (mysqld) Status: "SERVER_OPERATING" Tasks: 38 (limit: 11512) Memory: 466.4M CGroup: /system.slice/mysqld.service └─29142 /usr/libexec/mysqld --basedir=/usr Oct 02 13:19:20 localhost.localdomain systemd[1]: Starting MySQL 8.0 database server... Oct 02 13:19:20 localhost.localdomain mysql-prepare-db-dir[29057]: Initializing MySQL database Oct 02 13:19:32 localhost.localdomain systemd[1]: Started MySQL 8.0 database server. MySQL 8初始安全性 默认情况下，MySQL附带安全脚本，该脚本用于删除测试MySQL服务器数据库，删除数据库中的匿名用户，禁用远程root登录，设置密码强度，要运行脚本，只需执行命令;#mysql_secure_installation 运行时，系统会提示你是启用还是禁用密码策略，如果启用，请设置密码策略并创建与所选策略匹配的密码。VALIDATE PASSWORD COMPONENT can be used to test passwords and improve security. It checks the strength of password and allows the users to set only those passwords which are secure enough. Would you like to setup VALIDATE PASSWORD component? Press y|Y for Yes, any other key for No: y There are three levels of password validation policy: LOW Length >= 8 MEDIUM Length >= 8, numeric, mixed case, and special characters STRONG Length >= 8, numeric, mixed case, special characters and dictionary file Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2 Please set the password for root here. New password: Re-enter new password: Estimated strength of the password: 100 Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y 接下来，删除匿名用户，禁止远程root登录，删除测试数据库并重新加载权限表以完成所做的更改。 你现在可以使用上面设置的密码以root用户身份登录到MySQL 8服务器。mysql -u root -p 要显示mysql版本，可以运行查询;show variables like "version%"; +-------------------------+---------------------+ | Variable_name | Value | +-------------------------+---------------------+ | version | 8.0.13 | | version_comment | Source distribution | | version_compile_machine | x86_64 | | version_compile_os | Linux | | version_compile_zlib | 1.2.11 | +-------------------------+---------------------+
-
Linux手动导入导出Mysql数据库 MySQL数据库导出以root登录vps，执行以下命令导出。 #/usr/local/mysql/bin/mysqldump -u root -p phpcms > /home/backup/phpcms.sql 如果导出所有数据据，则语句为： #/usr/local/mysql/bin/mysqldump -u root -p –all-databases > /home/backup/all.sql 其中：root为数据库用户名 phpcms是需要导出的数据库名。 /home/backup/phpcms.sql是数据库文件sql导出的路径。MySQL数据库导入#/usr/local/mysql/bin/mysql -u root -p phpcms< /home/backup/phpcms.sql 导入所有数据库： /usr/local/mysql/bin/mysql -u root -p < /home/backup/all.sql
