286
edits
Changes
Jump to navigation
Jump to search
m
no edit summary
==MySQL commands for the MySQL console==
'''Always use mysql "console" instead of "phpmyadmin" for Security reasons!!!'''
If you want to show the size of all databases installed do:
login as mysql admin
<pre>
mysql -u admin -p
</pre>
at the mysql prompt do:
<pre>
mysql> SELECT table_schema "Data Base Name", sum( data_length + index_length) / 1024 / 1024
"Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema;
</pre>
Create database named "test":
<pre>
mysql> create database test;
</pre>
Delete database named "test":
<pre>
mysql> drop database test;
</pre>
Update/Reload Databases at MYSQL Server:
<pre>
mysql> flush privileges;
</pre>
Dump all Databases - Note: This do only if you want Restore the same Server, cause MySQL System Databases like "mysql or information_schema" are dumped too!! This will break on other Servers!
<pre>
mysql > mysqldump -u root -p --opt --all-databases > databases-dump-$date.sql
</pre>
mysql > Its better you write a Bash Script to dump every Database for its own! This does not scramble a fresh setup!
== User Handling ==
<pre>
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON db.* TO ‘user’@'localhost’;
UPDATE mysql.user SET Password=PASSWORD('newpassword') WHERE User='username'; FLUSH PRIVILEGES;
</pre>
== List Users ==
Login to MySQL Console as Admin, go into the System Database called mysql, and list.
<pre>
mysql -u mysqladminname -p
use mysql;
select * from mysql.user;
</pre>
'''Always use mysql "console" instead of "phpmyadmin" for Security reasons!!!'''
If you want to show the size of all databases installed do:
login as mysql admin
<pre>
mysql -u admin -p
</pre>
at the mysql prompt do:
<pre>
mysql> SELECT table_schema "Data Base Name", sum( data_length + index_length) / 1024 / 1024
"Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema;
</pre>
Create database named "test":
<pre>
mysql> create database test;
</pre>
Delete database named "test":
<pre>
mysql> drop database test;
</pre>
Update/Reload Databases at MYSQL Server:
<pre>
mysql> flush privileges;
</pre>
Dump all Databases - Note: This do only if you want Restore the same Server, cause MySQL System Databases like "mysql or information_schema" are dumped too!! This will break on other Servers!
<pre>
mysql > mysqldump -u root -p --opt --all-databases > databases-dump-$date.sql
</pre>
mysql > Its better you write a Bash Script to dump every Database for its own! This does not scramble a fresh setup!
== User Handling ==
<pre>
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON db.* TO ‘user’@'localhost’;
UPDATE mysql.user SET Password=PASSWORD('newpassword') WHERE User='username'; FLUSH PRIVILEGES;
</pre>
== List Users ==
Login to MySQL Console as Admin, go into the System Database called mysql, and list.
<pre>
mysql -u mysqladminname -p
use mysql;
select * from mysql.user;
</pre>