Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision		Previous revision
wiki:mysql [2024/04/21 12:04] – created 5.152.82.90wiki:mysql [2024/05/08 08:19] (current) – 5.152.82.90
Line 1: Line 1:
 +=====MySQL Conf=====
 +<code file .my.cnf>
 +[client]
 +host=127.0.0.1
 +database=db_name
 +user=db_user
 +password=db_passwd
 +</code>
 +
 =====MySQL Dump===== =====MySQL Dump=====
 +**Dump ALL databases**
 +<code>mysqldump -ER --triggers --tz-utc -e --default-character-set=utf8 --master-data=2 -r $(date '+%y%m%d-%H%M%S')-all-databases.sql --all-databases</code>
 +
 **Dump database without create db** **Dump database without create db**
-<code>mysqldump -ER --triggers --tz-utc -e --default-character-set=utf8 -r -r $(date '+%y%m%d-%H%M%S')-database.sql database</code>+<code>mysqldump -ER --triggers --tz-utc -e --default-character-set=utf8 -r $(date '+%y%m%d-%H%M%S')-database.sql database</code>
  
 **Dump database with create db** **Dump database with create db**
Line 12: Line 24:
 **Cut single table from dump** **Cut single table from dump**
 <code>sed -n '/^-- Table structure for table `licenses`/,/^--Table structure for table /p' database.sql > table.sql</code> <code>sed -n '/^-- Table structure for table `licenses`/,/^--Table structure for table /p' database.sql > table.sql</code>
 +
 +=====Useful commands====
 +**Purge Binlog**
 +<code>
 +BINLOG_FILE=$(mysql -e "SHOW MASTER STATUS"|tail -n1|awk '{print $1}')
 +mysql -e "PURGE BINARY LOGS TO '${BINLOG_FILE}'"</code>
 +
 +**DB Size**
 +<code>mysql -e "SELECT table_schema, SUM(data_length+index_length)/1024/1024 FROM information_schema.TABLES GROUP BY table_schema"</code>
 +
 +**Secure Installation**
 +<code>
 +mysql -e "UPDATE mysql.user SET Password = PASSWORD('NEWPASSWORD') WHERE User = 'root'"
 +mysql -e "DROP USER ''@'localhost'"
 +mysql -e "DROP USER ''@'$(hostname)'"
 +mysql -e "DROP DATABASE test"
 +mysql -e "FLUSH PRIVILEGES"
 +</code>

Trace: • kubernetes • nginx • adb • mongodb • mysql

Navigation

Devops
Devices
Hack
-
Nissan \ Smart
-
Config

Print/export
QR Code
QR Code wiki:mysql (generated for current page)