Home
Blog
About
Database administration
Operating systems
Development
Links


Following require login:
ScratchPad



Locations of visitors to this page


MySQL Snippets

List databases on server

mysql> show databases;
+------------------------+
| Database               |
+------------------------+
| mysql                  |
| test                   |
+------------------------+
2 rows in set (0.11 sec)

Get database size

SELECT
	table_schema "Data Base Name"
	,sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB"
	,sum( data_free ) / 1024 / 1024 "Free Space in MB"
FROM
	information_schema.TABLES
GROUP BY
	table_schema ;

-- On 5.1 (information_schema added in 5.0)
+--------------------+----------------------+------------------+
| Data Base Name     | Data Base Size in MB | Free Space in MB |
+--------------------+----------------------+------------------+
| information_schema |           0.00781250 |       0.00000000 |
| mysql              |           0.60974789 |       0.00088501 |
+--------------------+----------------------+------------------+

-- on 4.0.26
ERROR 1146 (HY000): Table 'information_schema.tables' doesn't exist
-- Yet to find a way of doing this in 4.0 

Copying MySQL Databases to Another Machine

MySQL Ref

  • Dump existing data

mysqldump -u user -p db-name > db-name.out

  • Copy db-name.out to remote server
  • Restore dtabase at remote server

mysql -u user -p db-name < db-name.out

Do not forget to copy the mysql database because that is where the grant tables are stored.
You might have to run commands as the MySQL root user on the new machine until you have the mysql database in place.

After you import the mysql database on the new machine,
execute mysqladmin flush-privileges so that the server reloads the grant table information.


Copyright HandyDBA 2012