Category Archives: MySQL

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

Mysql (MariaDB) force table name to lower case when create table

Stop Mysql service

  • run services.msc to open windows service manager
  • Stop MySQL service

Update my.ini (C:\Program Files\MariaDB 10.3\data\my.ini)

Add lower_case_table_names = 2

Start MySQL

  • Stop MySQL service

Reference: