Category Archives: MySQL

Get first row of each hour

First, get min id of each hour

Complete

 

Mysql Commands

 

Calculate DB sizes of Mysql on Linux

 

Dump Partial data of each table

//先切換到bin資料夾,因為沒有設定環境變數path
cd c:\program files\mariadb 10.4\bin

//目前限制100筆資料
mysqldump -u root -p –single-transaction –opt –where=”1 limit 100″ huede_hai > d:\backup\dump_hai_20190719.sql

Stored Function (Routine)

Reference:

Mysql dump and restore on windows

Steps:

  1. Dump
    1. cd c:\program files\mariadb 10.4\bin
    2. mysqldump -u root -p –all-databases > d:\backup\dump20190712.sql
      Notice:

      1. Execute this command in “command promp” not in “mysql client”
      2. No “;” in the end!
  2. Restore
    1. mysql u username p dbname < filename.sql
    2. mysql -u root -p < backup.sql

Reference:

  1. https://john-dugan.com/dump-and-restore-mysql-databases-in-windows/
  2. https://code.yidas.com/mysqldump/

Page log sequence number error of MariaDB

Cause

After reinstall mariadb, replace all DB files in folder “data” directly could cause this error and service “MariaDB” will never started.

Solution

  1. Check error log:
    https://mariadb.com/kb/en/library/what-to-do-if-mariadb-doesnt-start/
    C:\Program Files\MariaDB 10.4\data\REX-PC.err
  2. Create an option file and put it in %WINDIR%/my.ini
    https://dev.mysql.com/doc/refman/8.0/en/option-files.html

    1. Why 6?
      https://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
      https://mariadb.com/kb/en/library/innodb-recovery-modes/
  3. Run command to start mariadb: (under folder bin)
  4. You should be able to connect to DB server now
  5. Export tables in standard way.
  6. Uninstall DB server
  7. Install DB server

Reset MySQL root password on Windows

Steps

  1. Open command line with administrator permission
  2. Stop mysql service (2 ways)
    1. net stop mysql
    2. administrator tools -> services
  3. Create a mysql_init.txt file with this command line:
    ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘new password’;
  4. cd C:\Program Files\MariaDB 10.3\bin
  5. mysqld –init-file=C:\\mysql-init.txt
  6. Done, password of root has been reset

Reference:

  1. https://dev.mysql.com/doc/mysql-windows-excerpt/5.7/en/resetting-permissions-windows.html
  2. https://dev.to/oneearedmusic/access-denied-reset-mysql-root-user-password-2hk4