MySQL / MariaDB Superuser
Simple syntax to create a super user (for editing users and dropping schemas etc) CREATE USER 'superuser'@'%' IDENTIFIED BY ''; GRANT ALL PRIVILEGES ON *.* TO 'superuser'@'%' WITH GRANT OPTION;…
A bunch of ramblings about PHP programming, JQuery, MySQL and other 'stuff'
Simple syntax to create a super user (for editing users and dropping schemas etc) CREATE USER 'superuser'@'%' IDENTIFIED BY ''; GRANT ALL PRIVILEGES ON *.* TO 'superuser'@'%' WITH GRANT OPTION;…
You have some data which many users edit often, and you want to return a field with some sort of relative time stamp on it. For example, “27 minutes ago”…
So, quick and simple MySQL backup in a CRON. #!/bin/bash _hour=`date '+%H'`; _user="root"; _pass="thepassword"; _dest="/srv/backups/daily/"; mysqldump -u $_user -p$_pass --all-databases --ignore-table=mysql.event | gzip > $_dest_$_hour.sql.gz I’ve used this in an…
In most MySQL GUI’s that offer the ability to alter the table definitions of a schema, if you add a column that is an INT, if defaults the syntax to…
This is a “script” to convert your latin1 myisam database to utf8 innodb Dump the database mysqldump -u root -p database_to_convert > db.latin1.sql Make a copy of the dump cp…
MySQL allows you to profile queries, enabling you to see whats being run on your server. They are stored in a special profile table in the INFORMATION_SCHEMA database. You can…
So, once you’re all set up and running, you may want to give users access from the outside world. This is clearly an entry point for hackers and script kiddies,…
I recently needed to update a whole bunch of records using an IN() on the target table: UPDATE table1 SET field1 = 'some value' WHERE table_id IN ( SELECT table_id…
If you have a database of CDs, and artists, etc. Say you want to get a list of artists with their most recent releases: SELECT artist.name, album.title, album.release_date FROM artist…
Don’t leave the root user as ‘the’ user for your web application. That’s just not a good idea. For instance, if you are running a whole bunch of sites from…