Few MySQL helpful commands in MySQL maintenance
(MySQL rename Table, Empty MySQL Table Contents / Null Table
records, Get info about variables in a Table, Change record in
existing MySQL table, Get MySQL table privileges info, Some basic
commands for MySQL issues debugging)
This days I'm playing with MySQL trying out stuff. I decided it
could be helpful to somebody to share few things I learned.
So there we go:
1. To Rename MySQL existent table name
RENAME TABLE old_table_name to
new_table_name;
2. To completely wipe out the content of an existing Table in
MySQL
TRUNCATE TABLE table_name;
table_name = your table name to truncate
3. To RENAME column name in MySQL to another one
ALTER TABLE your_table_name CHANGE current_column_name
new_column_name VARCHAR(100);
Note that in the above example to rename column in MySQL you should
always specify the new column variable type e.g. VARCHAR(100)
or anything else you like.
4. To get information about a table e.g. variables and there
type in a MySQL table
DESCRIBE table_name;
5. To change some Value in a Column to another one based on
another value
UPDATE table_name column_name SET column_name='Lecturer' WHERE
other_column_name='some_value';
Here;
column_name = is your column name
other_column_name = is some other column_name which you're going to
search in for a certain some_value content
6. To get a thorougful information about MySQL table, it's
variables and the privileges
SHOW FULL COLUMNS from Table;
Here
Table should be your table name.
7. To get information about privileges of some mysql
user
SHOW GRANTS FOR your_user@host;
8. To create new user and grant certain privileges to some
Database
grant CREATE,INSERT,DELETE,UPDATE,SELECT on database_name.*
to username@localhost;
set password for username@host =
password('mysecretpassword');
Where:
database_name = is your desired database
username = is your user of choice
CREATE,INSERT,DELETE,UPDATE,SELECT = is your preferred
privileges to the database_name for the selected username
In case if you want to grant all possible user privileges that
could be assigned to a table use the following code:
GRANT ALL ON database_name.* TO username identified by
'mysecretpassword' with grant option;
9. Another really helpful few commands on daily basis whever
you're responsible for MySQL server are:
SHOW warnings;
Which is always helpful in debugging in MySQL.
And:
SHOW status;
SHOW processlist;
That two would inform you about the status of various key variables
and could also be a precious debugging tool.