Moving from MySQL to MySQLi or Pdo

How to change mysql to mysqli?

The first thing to do would probably be to replace every mysql_* function call with its equivalent mysqli_*, at least if you are willing to use the procedural API -- which would be the easier way, considering you already have some code based on the MySQL API, which is a procedural one.

To help with that, the MySQLi Extension Function Summary is definitely something that will prove helpful.

For instance:

mysql_connect will be replaced by mysqli_connect
mysql_error will be replaced by mysqli_error and/or mysqli_connect_error, depending on the context
mysql_query will be replaced by mysqli_query
and so on

Note: For some functions, you may need to check the parameters carefully: Maybe there are some differences here and there -- but not that many, I'd say: both mysql and mysqli are based on the same library (libmysql ; at least for PHP <= 5.2)

For instance:

with mysql, you have to use the mysql_select_db once connected, to indicate on which database you want to do your queries
mysqli, on the other side, allows you to specify that database name as the fourth parameter to mysqli_connect.
Still, there is also a mysqli_select_db function that you can use, if you prefer.

Once you are done with that, try to execute the new version of your script... And check if everything works ; if not... Time for bug hunting ;-)

How do I migrate my site from mysql to mysqli?

I advise you to read through this. A lot of helpful information for what you want to do, plus a tool to help. See: Converting to MySQLi.

It's just the PHP that changes.

Moving from MySQL to MySQLi

Instead of going with the flow, i care to suggest a PDO alternative

$db = new PDO($dsn, 'username','password'); 
              //$dsn is the connection string to your database.
              //See documentation for examples

//The next two rows are optional, but i personally suggest them to
//ease developing, debugging (the 1st) and fetching results (the 2nd)

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

$stmt = $db->prepare("SELECT * FROM table WHERE column1 = :c1");
$stmt->bindValue(':c1', 'ok'); //This example is trivial and not necessary
                               //but it gains relevance when the bound value 
                               //is a variable
$rows = $stmt->fetchAll(); //if you expect a single row use fetch() instead

//do something with the results

You can read more about PDO here: PDO manual

The biggest PDO advantage is that it's independent of the actual database in use by your application. If, by chance, you want to change database in the future, for example SQLITE or PostgreSQL, the only* change you have to make is your $dsn connection string

[*] True only if you used standard SQL queries and nothing vendor-specific.

Changing from MySQL to MySQLi or PDO

The security issues come from what is known as SQL injection. In order to understand how to make it more secure you have to first understand how that works. Using a basic example of a really bad query:

   mysql_query('SELECT FROM user WHERE username="'.$_POST['username'].'" AND password="'.$_POST['username'].'"');

There are two main things wrong with this query. The first and most obvious is that values are added with no sanitation done on them. This is a classic example of SQL injection. In this example a user could submit a username, like this..

 'admin" --';

Now what is so bad with that? Because we are not filtering this (mainly for the quote) it makes our query look like this:

 SELECT * FROM user WHERE username="admin" --" AND password="'.$_POST['username'].'"

To fully understand this you have to know that the -- is the start of an inline comment ( much like // in PHP), and nothing after it is ran by the database. Given that, this is what we are left with.

 SELECT * FROM user WHERE username="admin"

Now if we were doing that for password validation by seeing if it returned a row, and had an account named admin which isn't unreasonable, we would have someone logged in without ever using a password.

The second issue that is less obvious is sending the password and using the query to validate. If we had structured it this way (psudo code):

   SELECT password FROM user WHERE username="admin"
   if( returned password == submitted password )

They would still need a password, because we are doing the evaluation in our code (application layer) and not in the database.

mysql, mysqli or PDO will not protect you if you inject variables. PDO and MySQLi allow you to use prepared statements that take care of that issue. PDO is the better of the two if you ask me for the following reasons.

Not dependent on the MySQL database (database agnostic)
Allows named place holders, such as :name instead or just ? indexed placeholders
Better OOP support
Generally supports more features

But neither one can keep you safe if you don't understand the threat.

Moving from mysql connection to mysqli

You have all the time in the world since they will never stop working on their own!
Yes, there are several ways of doing this.
Yes, but there is no one-size-fits-all solution. Every situation is different and what's proper for you particular situation may not be proper for every situation.

First, the old ext/mysql is deprecated as of PHP 5.5.0, but it will never stop working entirely as the extension itself will eventually be moved into the PHP PECL repository (when it comes time to remove it). However, we're not there yet and you can only be affected when and if you chose to upgrade to that version of PHP. There is no exact time determined for the removal of the extension.

Second, you can use a variable to store the database connection just as the old ext/mysql extension was doing for you behind the scenes. The trick was you weren't aware of what it was doing (it uses the last open connection you created when you called mysql_connect and uses that everytime you call something like mysql_query to access the database).

You can do this with a static variable in your function using procedural style....

function openDBConn() {
    static $link;
    if (!isset($link)) {
      $link = mysqli_connect('localhost', 'my_user', 'my_password', 'my_db');
    }
    return $link; // returns the link
}

Or you can do this with a Class Static Variable using OOP...

Class MyDBConnect {

    public static $link;

    public function openDBConn() {
        if (!isset(static::$link)) {
            static::$link = new mysqli('localhost', 'my_user', 'my_password', 'my_db');
        }
    }

}

I want to encourage you for using the newer APIs and I commend you for it, but I also want to caution you as you move forward. When you start to port your functions over from the old ext/mysql functions to the new mysqli extension be careful not to also port over the bad practices of the old extension as well (such as using the old SQL string concatenation and escaping techniques ext/mysql offered). Instead take advantage of MySQLi prepared statements and parameterized queries.

What I do want to direct your attention to are the benefits of using the newer APIs to interface with your MySQL database (namely PDO and MySQLi).

should i upgrade from mysql to mysqli of PDO?

Some day you will! As mentioned in comments it depends. Feeling its the time? then yes, it can help.

But if you upgrade your PHP you have to move to Mysqli or PDO. Both are good enough but moving to Mysqli is easier and needs minor changes in your code.

Mysqli is pre-configured for MySQL but PDO supports other databases, Mysqli has both procedural(as mysql) & object-oriented(as PDO) interfaces.

You may also check PHP doc about choosing APIs here