zeldor.biz

Linux, programming and more

Copyright © 2023
Log in

Migrate MySQL users

December 24, 2020 by Igor Drobot Leave a Comment

Following line will query all mysql user with their grants and print the creation statements for them.

By copy pasting or by filling a SQL-file, you can easily migrate all MySQL-user to a new mysql or mariaDB instance.

mysql -u root -p"YOUR_PW" -Ne "select distinct concat( \"SHOW GRANTS FOR '\",user,\"'@'\",host,\"';\" ) from mysql.user;" mysql | \
mysql -u root -p"YOUR_PW"| \
sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'

Filed Under: Linux Tagged With: MariaDB, Migrate MySQL User, MySQL, SQL

MySQL/MariaDB replication

December 24, 2020 by Igor Drobot Leave a Comment

mysql-replication

MySQL-Replication could be a kind of load balancing for querieng applications or active failover/backup node which has the same data.

Also for common heavy loaded scenarios, where the primary write-queries are going to the master node and all read-queries are running against the slave node.

[Read more…]

Filed Under: Linux, MySQL Tagged With: Debian, MySQL, MySQL replication, MySQL slave

Nextcloud meets MariaDB

August 8, 2018 by Igor Drobot Leave a Comment

nextcloud

SQLite is good for testing and simple single-user Nextcloud instance,
but it does not scale for multiple-user production cases. For the first Installation its quiet simple and reliable.

After some years of usage my SQLite-database reached a size of 130MB and the performance was not more the same as after the first days.

You can convert a SQLite database to a more performing MariaDB database with the Nextcloud command line tool:
[Read more…]

Filed Under: Networking Tagged With: MariaDB, MySQL, Nextcloud, sqlite

InnoDB recovery

August 12, 2017 by Igor Drobot Leave a Comment

InnoDB recovery

In some cases databases gets corrupted(shit happens). After a hardware fail or not correct usage like a “kill -9” of mysqld process without gracefully flushing of opened tables.

After the start of mysqld.service, you can get a log-entry in your mysqld-log file, which informs you about the corruption of database, like this one “corruption in the InnoDB tablespace“.
In some cases mysqld.service will crash every n-minutes..

Independently of the symptoms the crashed database should be repaired immediately!
[Read more…]

Filed Under: Linux Tagged With: InnoDB, InnoDB recovery, MariaDB, MySQL

MySQL Dump

October 11, 2011 by Igor Drobot Leave a Comment


Collection of some MySQL commands, very useful to create and restore MySQL-Backups.
[Read more…]

Filed Under: Debian, Linux, MySQL Tagged With: MySQL, MySQL Backup, MYSQL Dump, mysqldump, mysqldumper

  • 1
  • 2
  • 3
  • Next Page »