Differences

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

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
fuss:mysql [2015/08/28 07:23]
office [Reset Root Password]
fuss:mysql [2017/02/22 18:30] (current)
Line 1: Line 1:
 +====== List Users ======
  
 +<code sql>
 +SELECT user FROM mysql.user;
 +</​code>​
 +
 +====== Reset Passwords ======
 +
 +Sets the password ''​secretpassword''​ for user ''​someuser''​.
 +
 +<code sql>
 +UPDATE mysql.user SET password=PASSWORD('​secretpassword'​) WHERE user='​someuser';​
 +</​code>​
 +
 +====== List the Size of Databases ======
 +
 +<code sql>
 +SELECT table_schema "​Database",​ Round(Sum(data_length + index_length) / 1024 / 1024, 1) "​MB"​ FROM information_schema.tables GROUP  BY table_schema;​
 +</​code>​
 +
 +====== Select Random Records ======
 +
 +The following query, selects one random entry from the table ''​table''​.
 +
 +<code sql>
 +SELECT * FROM table ORDER BY RAND() ​ LIMIT 1
 +</​code>​
 +
 +====== Select Using RegExp ======
 +
 +This query will select ''​lastname''​ and ''​firstname''​ from the table ''​employees''​ with the condition that the first character of ''​lastname''​ match either of the uppercase letters ''​M'',​ ''​B''​ or ''​T'':​
 +
 +<code sql>
 +SELECT lastname, firstname FROM employees WHERE lastname REGEXP ​ '​^(M|B|T)';​
 +</​code>​
 +
 +====== Comma-separated Values ======
 +
 +A structurally simple format is the ''​CSV''​ format that is supported by many data processing software packages (Excel, etc...) and MySQL can both import and export from ''​CSV''​.
 +
 +===== Export =====
 +
 +This line exports data from the table ''​account''​ to the file ''/​tmp/​dump.csv''​ in a comma-separated value format.
 +
 +<code sql>
 +SELECT * FROM account INTO OUTFILE '/​tmp/​dump.csv'​ FIELDS TERMINATED BY ','​ LINES TERMINATED BY '​\r\n';​
 +</​code>​
 +
 +===== Import =====
 +
 +Imports are preformed symmetrically,​ by preserving the ''​FIELD''​ and ''​LINES''​ setting:
 +<code sql>
 +LOAD DATA INFILE '/​tmp/​dump.csv'​ INTO TABLE account FIELDS TERMINATED BY ','​ LINES TERMINATED BY '​\r\n';​
 +</​code>​
 +
 +====== Dump and Restore a Database ======
 +
 +To dump a database to a file, issue:
 +<code bash>
 +mysqldump -u root -p database > database.sql
 +</​code>​
 +
 +to restore a database from a file, issue:
 +<code bash>
 +mysqldump -u root -p database < database.sql
 +</​code>​
 +
 +====== Sum-Up Columns in MySQL Table ======
 +
 +Given the following table:
 +
 +^ ''​Name'' ​      ^ ''​Score'' ​ ^
 +| ''​Amanda Lay''​ | ''​5'' ​     |
 +| ''​Ty Tass'' ​   | ''​11'' ​    |
 +| ''​Ty Tass'' ​   | ''​2'' ​     |
 +| ''​Lou Sirr'' ​  | ''​7'' ​     |
 +| ''​Amanda Lay''​ | ''​4'' ​     |
 +| ''​Lou Sirr'' ​  | ''​20'' ​    |
 +
 +we want to find out the total score for ''​Ty Ass''​. We can then write the SQL query: ​
 +
 +<code sql>
 +SELECT Name,​SUM(Score) AS Total FROM table WHERE Name='​Ty Ass';
 +</​code>​
 +
 +which will return the following table:
 +
 +^ ''​Name'' ​      ^ ''​Total'' ​ ^
 +| ''​Ty Tass'' ​   | ''​13'' ​    |
 +
 +The same can be done if the scores are distributed on multiple columns:
 +
 +^ ''​Name'' ​      ^ ''​Crouched''​ ^ ''​Sideways''​ ^  ''​Laying''​ ^
 +| ''​Amanda Lay''​ | ''​5'' ​       | ''​7'' ​       | ''​30'' ​     |
 +
 +To find out the total, summative score for ''​Amanda Lay'',​ we use the query:
 +
 +<code sql>
 +SELECT Name,​(Crouched + Sideways + Laying) AS Total FROM table WHERE Name='​Amanda Lay';
 +</​code>​
 +
 +which will return the table:
 +
 +^ ''​Name'' ​      ^ ''​Total''​ ^ 
 +| ''​Amanda Lay''​ | ''​42'' ​   |
 +
 +====== Empty a Database Without Dropping ======
 +
 +To empty the database ''​mydatabase'',​ issue:
 +
 +<code sql>
 +SELECT CONCAT('​DROP TABLE IF EXISTS ', table_name, ';'​) FROM information_schema.tables WHERE table_schema = '​mydatabase';​
 +</​code>​
 +
 +====== Repair MySQL Tables ======
 +
 +<code bash>
 +mysql -Be "​select concat('​repair table ', TABLE_SCHEMA,​ '​.',​ table_name, ';'​) from information_schema.tables where table_schema<>'​information_schema';"​ | tail -n+2 | mysql
 +</​code>​
 +
 +or
 +
 +<code bash>
 +mysqlcheck --auto-repair -A
 +</​code>​
 +
 +====== Reset Root Password ======
 +
 +First, stop MySQL using:
 +<code bash>
 +/​etc/​init.d/​mysql stop
 +</​code>​
 +
 +Now we run MySQL by omitting to read the grant tables:
 +<code bash>
 +mysqld --skip-grant-tables
 +</​code>​
 +
 +And we log-in as ''​root'':​
 +<code bash>
 +mysql -u root
 +</​code>​
 +
 +Then we run a query to replace the root password:
 +<code sql>
 +FLUSH PRIVILEGES;
 +UPDATE mysql.user SET Password=PASSWORD('​password'​) WHERE User='​root';​
 +FLUSH PRIVILEGES;
 +exit
 +</​code>​
 +
 +And after that we restart MySQL:
 +<code bash>
 +/​etc/​init.d/​mysql restart
 +</​code>​
 +
 +====== Import Multiple SQL Files ======
 +
 +In a directory containing ''​.sql''​ files, run:
 +<code bash>
 +ls -1 *.sql | awk '{ print "​source",​$0 }' | mysql --batch -u username --password=password database
 +</​code>​
 +
 +where:
 +  * ''​username''​ is the user name allowed to import
 +  * ''​password''​ is the password for the user
 +  * ''​database''​ is the database to import the data to
 +
 +====== Drop all Tables in Database ======
 +
 +This can be accomplished with:
 +
 +<code bash>
 +mysql -u USER --password=PASSWORD -Nse 'SHOW TABLES'​ DATABASE | while read table; do mysql -u USER --password=PASSWORD -e "DROP TABLE $table"​ DATABASE; done
 +</​code>​
 +
 +where:
 +
 +  * ''​USER''​ is the username to use
 +  * ''​PASSWORD''​ is the password for the user
 +  * ''​DATABASE''​ is the database to drop all the tables in
 +
 +====== Truncate all Tables in Database ======
 +
 +This can be done with:
 +<code bash>
 +mysql -u USER --password=PASSWORD -Nse 'show tables'​ DATABASE | while read table; do mysql -u USER --password=PASSWORD -e "​TRUNCATE TABLE $table"​ DATABASE; done
 +</​code>​
 +
 +where:
 +
 +  * ''​USER''​ is the username to use
 +  * ''​PASSWORD''​ is the password for the user
 +  * ''​DATABASE''​ is the database to truncate all the tables in

fuss/mysql.txt ยท Last modified: 2017/02/22 18:30 (external edit)

Access website using Tor Access website using i2p


For the copyright, license, warranty and privacy terms for the usage of this website please see the license, privacy and plagiarism pages.