Grant Privileges To User in MySQL


If you would like to add a user to MySQL and give them permissions to view on or multiple databases, then continue reading. It is a fairly simple process and can be very powerful in keeping your database secure as well as giving people the access to the database they need.

Login To MySQL Server

In order to run these sql commands (sql queries), login to your sql database so we can take a look at the format of the MySQL command.

mysql -u <user> -p

I recommend you don’t enter your password above as people viewing your bash history would see your password. Once you hit enter you will be prompted to enter your password.

Grant Privileges To User in MySQL

Now the format of the grant appears as follows

GRANT <privilege-type> ON <database> TO <user>@`<ip-or-domain>` IDENTIFIED BY '<new-password>'

Here is the breakdown of what each of the variables means.

  • privilege-type: type of privileges to give such as INSERT, DELETE, ALTER, DROP, ALL PRIVILEGES, etc.
  • database: the database/table combination your are giving the user access to (see below for examples).
  • user: username of your choice that the user will use to login to view their databases.
  • ip-or-domain: the location where the user is access the database FROM.
  • new-password: password of your choice that the user will use to login to view their databases.


MySQL GRANT Privileges Examples

Example 1: Gives the user `poweruser` full access to all databases and tables. The *.* means all databases and all tables.

GRANT ALL PRIVILEGES ON *.* TO poweruser@`` IDENTIFIED BY 'some-pass';


Example 2: Create a user with INSERT, DELETE and UPDATE permissions to all tables under the exampledatabase database. They can also only login if they come from the location

GRANT INSERT,DELETE,UPDATE ON exampledatabase.* TO `editoruser`@`` IDENTIFIED BY 'any-pass';


Example 3: Only give delete to a user and they can only access the reports database and the temptable table. The % for their location means they can access it from anywhere and not just a single ip or domain.

GRANT DELETE ON reports.temptable TO `cleanupuser`@`%` IDENTIFIED BY 'their-pass';


Example 4: Gives the root user full access but only from the same location as the database. So anyone outside of the server won’t be able to login. Useful if your website is running on the same server as the database and very secure.

GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED BY 'difficult-pass';


MySQL Copy Table From One Database To Another


So you are looking at transferring the data of on MySQL Table into another MySQL Table. Migrating data from database to another database table can be surprisingly easier than you would think.

MySQL Copy Table From One Database To Another

The following query will copy all rows from table_one into table_two. In order for this to work correctly, the number of columns must match. It doesn’t matter if the names of the columns are different, so long as the column count is the same.

INSERT INTO `table_two` SELECT * FROM `table_one`;

MySQL Copy Table With Different Number Of Columns

You can also copy data where the column count is different between the two tables. In order to achieve this, your SELECT will have to return the right number of columns as INSERT statement is expecting.
The exciting part of this is that you can filter which results get transferred by using normal MySQL “SELECT WHERE” syntax as you can see in the below example. In the example, we only transfer the records from table one that are LIKE ‘some value’ and limit the transfer to 20 results.

INSERT INTO `table_two` (`column_A`, `column_B`) SELECT `column_C`, `column_D` FROM `table_one` WHERE `column_name` LIKE '%some value%' LIMIT 20;

Important Note

In the above query, you will notice I select column_C and column_D but I insert it into column_A and column_B. This will work fine even thought the column names don’t match. As long as the column count is the same you will be fine. Assuming that you don’t have foreign key conflict or are missing required columns.

Simple as that.

Why Unix Timestamp Is Useful


Unix timestamps are a way or storing a specific date and time. Unix timestamps are a 10 digit number that represents the number of seconds that have passed since midnight Universal Co-ordinated Time (UTC) of January 1st, 1970.

What makes timestamps ever so useful is that since timestamps are recorded as of UTC (or GMT) a single time stamp can be used to to represent all time zones. Once you have your timezone, the unixtime stamp will adjust the time accordingly. For example, a unix timestamp of 1304951846 can represent 10th May at 0:37 AM in London, but it would also represent 10th May at 10:37 AM in Brisbane/Australia.

Why Unix Timestamp Is Useful

How can this be useful? Just think, users can see dates and times on your website relative to their own timezone, rather than the timezone of the server the website is being run on. Being a web developer, I am forever finding myself storing dates do be displayed back to the user. It may be a date a user last logged into a program or website, perhaps even a date that a user posted an article such as this to a website. Once we store a specific timestamp, we can display the time and date to users but also in the correct relevant timezone. On top of which, timestamps are easy to use, and many languages support them.

Online Timestamp Converter

There are online unix timestamp generators to convert to and from unix time stamps such as the one found here.

PHP can convert a timestamp to a human readable format by using the date() function and likewise using strtotime() and mktime() to convert a natural language string to a unixtime stamp to be stored. Short examples are:

PHP Timestamp Example


/* convert unixtime stamp to human time */
echo date('d F Y H:i:a',1304951846); // would display a time of 10 May 2011 00:37:am

/* convert to a unix time stamp */
echo strtotime('+1 Year'); // would return the timestamp of 1 year from today
echo mktime($hour,$min,$sec,$month,$day,$year); // echo a timestamp specified by the variables


MySQL Timestamp Example

MySQL uses the syntax FROM_UNIXTIME to convert data to a human readable format for example:

SELECT *,FROM_UNIXTIME(timestamp_column) FROM `table`;

Free Online Unix Timestamp Converter

Generate your own unix timestamp online using an Online Unix Timestamp generator.

MySQL REPLACE – Replacing text in MySQL


MySQL replace is a mysql function that allows you to replace one bit of text with another piece of text during a MySQL query. The best way to understand how a MySQL REPLACE Function works is to see it work as you will see in the example below.
The MySQL replace function works similar to how the PHP str_replace function works however the parameters are in different order.


The function is called mysql replace() and takes 3 parameters which are:

  1. the mysql column name
  2. the text you want to be replace
  3. the text to replace the existing text with

For example, the function will look something like this:

REPLACE(column_name, 'replace_this', 'with_this_text');

Example 1 – SELECT and REPLACE

In this example, any results that have John as their first name will instead return Johnathon as their firstname and any other names will not be affected.

SELECT REPLACE(`firstname`,'John','Johnathon') AS firstname,lastname,age FROM userdatabase;


Example 2 – UPDATE and REPLACE

This will update and switch one bit of text with another. Here we are going to change some html and replace <i> tags with <em> tags.

UPDATE wp_posts SET post_content = REPLACE(post_content,'<i','<em');

Now all the italic html tags would have ben replace with emphasized tags.

Real World Unix Timestamp Examples


Unix timestamps are extremely useful and can be used across my different programming languages and platforms. I will outline a couple programming languages that support timestamps.

  • PHPconvert timestamps to date in php
  • MysQLview example on how you can query timestamps from a MySQL Database
  • Javascriptthe Date() function in Javascript can use timestamps
  • Perlthe time() function in Perl can handle unix timestamps
  • Linuxreturn current timestamp in linux

Real World Unix Timestamp Examples


In PHP, you can convert a unix timestamp to a real time by using the php date() function. date() allows you to convert a timestamp that is supplied as the second parameter or if the second parameter is left out, it will convert the current timestamp to date.


/* this would display a time of 23 May 2011 17:12:pm */
echo date('d F Y H:i:a',1306134726);
/* and this would display the current time in the same format as above */
echo date('d F Y H:i:a');


The way the date is displayed is controlled by the flags passed to the function. Here we used the flags ‘d’, ‘F’, ‘Y’, ‘H’, ‘i’ and ‘A’. For a complete list of flags and to see a complete description of this function, visit
The PHP function time(), strtotime() and mktime() all return the current unix timestamp. If you wanted to, you can also get a time stamp 24 hours from now by using the following code.


/* get tomorrows timestamp */
$tomorrow = time() + (24 * 60 * 60);

/* or you can use strtotime to convert a natural string into a timestamp */
$tomorrow = strtotime('+24 hours');

/* and this will create a timestamp based on the date and time variables */
$timestamp = mktime($hour,$min,$sec,$month,$day,$year);



If you have a table that contains a column with a unix timestamp in it, you can convert it using the query below. It will return a column that contains rows of easy to read date and times.

SELECT FROM_UNIXTIME(column_name) AS real_time FROM `table_name`;


Javascript has a function called ‘Date()’ which allows you to use timestamps in javascript. Remember that since javascript is client side code (i.e. runs on the persons browser), the date shown is their own computer time, not the server time. To get the current timestamp in javascript, simple use

<script type="text/javascript">

/* get the current timestamp */
var timestamp = +new Date();


You can also convert a timestamp to a date by using the following javascript date functions.

<script type="text/javascript">

/* create a new date as of the current date */
var date = new Date();
/* you can also create a new date and pass a timestamp to create the date as of the timestamp */
var another_date = new Date(1306482441);

var day  = date.getDate(); // get the day that date relates to
day = day < 10 ? '0' + day : day; // add a 0 if less that 10
var month = date.getMonth() + 1; // returns month as 0 - 11
var year = date.getFullYear(); // 4 digit year (eg. 2011)
var hour = date.getHours(); // get hours
hour = hour<10?'0'+hour:hour; // pad with a 0
var minute = date.getMinutes(); // and minutes
minute = minute<10?'0'+minute:minute; // pad with 0
var second = date.getSeconds(); // get seconds
second = second<10?'0'+second:second; // pad with 0



In order to retrieve the current timestamp in Perl, you can make use of the time() function which is the same as PHP.

my $timestamp = time();


Using the ‘date’ command in linux will print out the date specified by the parameters passed to it. We will use the %s flag to tell it to print the seconds passed since ‘1970-01-01 00:00:00 UTC’ (i.e. it will print out the current timestamp).

date +%s



In MySQL, when you perform a mysql insert query, it is very common to come across errors with one of the insert queries. Once an issue is encountered with a mysql insert, the rest of the mysql inserts will not be executed since the MySQL query won’t continue. This can be annoying since you will have to figure out which rows were inserted and which ones weren’t so you can manually insert the remaining rows.

Fortunately, you can use mysql insert ignore syntax to avoid this frustration by using the keyword ‘ignore’.


Consider the following mysql insert statement that would insert two records:

INSERT INTO `table` ('name','surname','email') VALUES ('Mark','Ringo',''),('Sarah','Ainsley','');

If the first insert (mark ringo) had to fail for some reason, then Sarah would not be inserted UNLESS you add the word ‘ignore’ after mysql insert statement, making the above statement:

INSERT IGNORE INTO `table` .......

Now if the first entry fails, then Sarah will still get inserted. Pretty nifty eh’ and can save countless hours of frustration while trying to import partial records and missing rows from an incomplete mysql insert.

Use With Caution

Please use the mysql insert ignore with caution. Errors and warnings that creep up are there for a reason. MySQL INSERT IGNORE can save a lot of frustration and simplify inserting records however it can end up masking some issues that exist.



Performing MySQL queries on your websites or server can cause long delays in a page loading or script executing while the MySQL INSERT or MySQL UPDATE finishes executing. This causes long delays for the user or possibly delays the rest of a script from running until the MySQL query completes (or errors out). Generally the delay on mysql inserts or update is minimal however on large complex queries with lots of join across multiple tables and millions of rows of data (or badly indexed tables), there will be a significant pause while the query completes.

Solution to Long Queries

If there is no need for the user to see the result of the query, such as queries that are involved with logging statistics or building hidden reports, then it would be highly recommended to use mysql delayed insert or mysql delayed update syntax. Using MySQL DELAYED INSERT and MySQL DELAYED UPDATE to send the queries into the queue and run in the background allowing the page or script to continue executing. It is simple to use and can be implemented by adding the `delayed` keyword after INSERT or UPDATE command and the sql query will be queued by the MySQL server to be run in the background and the page while continue loading while the sql query is executed in the background as the example below shows:


MySQL Insert statements can be run in the background by using the following syntax:

INSERT DELAYED `some massive time consuming query`;


MySQL Update statements can be run in the background by using the following syntax:

UPDATE DELAYED `some massive time consuming query`;


Now the page will continue to load while the MySQL server continues to execute the query which can drastically speed up page loading speeds or script execution times. Bear in mind that if the user needs to see the result, then you won’t be able to use mysql delayed inserts.

Get Last Day Of Month MySQL And PHP

In order to get last day of month in mysql or php, you can use built in functions. As you will soon see below it is a rather straight foward task. I will show you two examples on how to get the last day of the month in mysql as well as php. In php you can use the date() function and in mysql we will make use of the last_day() method.

Get last day of the month PHP

PHP has a date function which returns a string representation of the date formatted according to the expression passed to it. So the simple way is to use the ‘t’ identifier in the date function which will return the last day of the month.

echo date('Y-m-t',strtotime('2014-01-12')); // echo's 2014-01-31
echo date('t M Y'); // echo's 31 Jul 2014

Get last day of month MySQL

MySQL has a function called last day that will return the last day of the date supplied to it. It can be the date of the current month or any other month.

SELECT last_day(CURDATE() - INTERVAL 1 MONTH) AS last_month;
SELECT last_day(CURDATE()) AS current_month; 
SELECT last_day(DATE('2014-03-12')) AS march_month


As you can see, getting the last day of the month is a simple task. If you get stuck, let me know in the comments.