MySQL

Update the Auto Increment Value of a Table in MySQL

Sometime you need to change the Auto-increment value in MySQL so that the next inserted record has a different value. In this case, the we want to change the last order id in an online shop to something a bit higher so it looks like more orders have gone through. To see the last inserted value, use this, SELECT Auto_increment FROM information_schema.tables WHERE table_name=’uc_orders’; or SHOW TABLE STATUS LIKE ‘uc_orders’ To set the value, alter…

Continue reading

MySQL

Auto-login for MySQL and MariaDB

When you log into MySQL at CLI, you have to define the username and password mysql -uusername -ppassword [database] You can add these to a config file (.my.cnf) in the users home directory vim ~/.my.cnf and add these lines (changing the user and password of course) [client] user=root password=password and then you jus need to type mysql [database] and you are in. You can even define the database and other settings with some extra lines……

Continue reading

MySQL

Convert an Epoch date to Readable date in MySQL

Basic format SELECT FROM_UNIXTIME(epoch) as value FROM mytable Will display in the format like 2012-01-18 05:28:03 Or if you want to format the date specifically SELECT FROM_UNIXTIME(epoch,”%Y-%m-%d”) as value FROM mytable Or to insert into a table INSERT INTO mytable VALUES (UNIX_TIMESTAMP(now())) or use YYYY-MM-DD HH:MM:SS : INSERT INTO mytable VALUES (UNIX_TIMESTAMP(‘2008-12-01 12:00:00’)) Check out the Epoch Converter for more info.

Continue reading

Gerrardism MySQL

MySQL is NOT a Relational Model Database

I was listening once to two programmers talk about MySQL. Both had taken the job in smaller business to get more “hands on” and “big picture” to developing high end web sites. They had both worked on big sites before – but only on a very small component. They where talking about MySQL and referred to it as being a Relational Model Database (RMDBS). It is not! Well, not in it normal form. And definitely…

Continue reading

MySQL

Get Size of Tables or Database in MySQL

Get Size of Tables in a single MySQL Database For a given schema_name (aka database) SELECT table\_name, table\_rows, data\_length, index\_length, round(((data\_length + index\_length) / 1024 / 1024),2) “Size in MB” FROM information_schema.TABLES WHERE table\_schema = “schema\_name”; Get Total size of MySQL Databases 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 ;

Continue reading

MySQL

Log All Queries on an MySQL Database

I need to see every query that is being run on my MySQL database to make sure an attached device is working. Edit the MySQL Configuration file sudo vim /etc/mysql/my.cnf And add the following line log = /var/log/mysql/mysql.log Sometimes this is already on the fie and commented out. Now restart MySQL sudo service mysql restart or sudo /etc/init.d/mysql restart. You can view the SQL commands as they come in live with this command; tail -f…

Continue reading