[MySQL] #Tips

MySQL.md

Where are the logs?

SHOW  GLOBAL VARIABLES LIKE '%log%';

Where is the my.cnf in MacOSX?

There is no one.If you need one in /usr/local/mysql/support-files/, and cp one to /etc/

About mac connection.

• Checking : show variables like 'max_connections';
• Setting : set global max_connections=1000;

How to modify field type?

alter table `tablename` change fieldname fieldname varchar(500);

How to modify field comment?

alter table `tablename` modify column fieldname int comment 'New comment';

How to copy a table?

create table picture_bak select * from picture;

How to change a table's auto_increament id?

alter table users AUTO_INCREMENT=123456;

How to rename a table?

RENAME TABLE old_table TO new_table

This action is very fast, even there are more than 70,000,000 records in the table according to my real experience.

How to learn the size of the whole database?

SELECT SUM( data_length ) AS total
FROM information_schema.TABLES
WHERE information_schema.TABLES.TABLE_SCHEMA =  'dbname'

How to change table engine?

alter table table_name engine = InnoDB

Get mysql's status.

show status;
show status like "Connection%";
show status where Value > 0;