Don’t worry, this is an easy to recover from situation. Just follow these basic steps to reset the root user password.

Stop the MySQL server process.

Start the MySQL server process with the –skip-grant-tables option. This option causes the server to start without using the privilege system at all, which gives anyone with access to the server unrestricted access to all databases. BEWARE! Anyone can access your server, so make sure off the Internet, and that you perform the following steps as quickly as possible.

Start the MySQL console client with the -u root option. Mysql -u root

SELECT * FROM mysql.user;

UPDATE mysql.user SET Password=PASSWORD(‘[password]‘) WHERE User=’[username]‘; Replace [username] with root to change root. You can also change any other user as well.

Stop the MySQL process

Start the MySQL Process normally (i.e. without the –skip-grant-tables option).

Some folks have reported that issuing a Flush Privileges command will prevent your having to perform the final Stop/Start of the MySQL server. However, this has not always worked for me, and I think to be safe, it is wise to recycle the server.

Luckily, there is a fairly easy way to speed up shutdown while still allowing the server to stay up and handle requests.  The secret is to lower the maximum percentage of dirty pages allowed.  This is controlled by the global variable innodb_max_dirty_pages_pct. On my servers I usually have this set at 75%. This variable can be changed at run-time, which allows us to modify this variable to speed up system shutdown.

First, enter the command :

set global innodb_max_dirty_pages_pct=0;

You can then enter the following to verify the new value:

show global variables where variable_name rlike ‘dirty’;

To monitor the number of dirty pages that have not been written to disk, enter the following:

show global status where variable_name rlike ‘dirty’;

You will see this number begin to drop, although it is unlikely to ever reach 0, once it drops to a low level and reaches a plateau, you can then shutdown the server. You will find that the server will shutdown very quickly, allowing you to minimize downtime.

select * from mytable where first_name collate latin1_general_cs=’damon’

This will only return rows will the first_name matches damon exactly. Damon will not match.

You can also use latin1_bin which performs an exact binary match.

If you are unsure of a field or strings collation, you can use the COLLATION(str) function to return the collation of the string argument.

Related articles

• MySQL – Disabling Binary Logging for INSERT and UPDATE (prodromus.com)

If you need to change your password for your localhost root account, here is the proper syntax.

mysql -u root
mysql> SET PASSWORD FOR 'ROOT'@'LOCALHOST' = PASSWORD('new_password');

You can also grant access to all remote servers as well by using the following syntax.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;


By default, MySQL only allows access by the ‘root’ account from the host running the database server (‘localhost’).

The percent symbol ("%") in the notation root@"%" means “any host”, but it doesn’t imply localhost. You need to repeat the commands above with root@localhost in order to grant/revoke permissions for localhost.

If you are setting up your server and require NO remote access, add skip-networking to your my.cnf file.

skip-networking : Don’t listen for TCP/IP connections at all. All interaction with mysqld must be made via Unix sockets. This option is highly recommended for systems where only local requests are allowed. Since you need to allow remote connection this line should be removed from my.cnf or put it in comment state.

So, what is going on?  Well, by default the MySQL statement delimiter is the semi-colon (;).  However, the mysql command-line utility also uses a semi-colon as a delimiter. So, if the command-line utility were to interpret the ; characters inside of the stored procedure itself, those would not end up becoming part of the stored procedure, and that would make the SQL in the stored procedure syntactically invalid.

The solution is to temporarily change the command-line utility delimiter using the DELIMITER command, as seen here:

DELIMITER //

CREATE PROCEDURE sku_pricing( OUT low_price DECIMAL(8,2), OUT high_price DECIMAL(8,2), OUT avg_price DECIMAL(8,2) )

BEGIN

SELECT Min(prod_price) INTO low_price FROM sku_items;

SELECT Max(prod_price) INTO high_price FROM items;

SELECT Avg(prod_price) INTO avg_price FROM items;

END; //

DELIMITER ;

Here, DELIMITER // tells the command-line utility to use // as the new end of statement delimiter, and you will notice that the END that closes the stored procedure is defined as END // instead of the expected END;. This way the ; within the stored procedure body remains intact and is correctly passed to the database engine, rather than being interpreted by the client. And then, to restore things back to how they were initially, the statement closes with a DELIMITER ;.   Any character may be used as the delimiter except for \, as it is an escape character in mySQL.

Simply define IP as an INT UNSIGNED.

Use MySQL’s built-in function INET_ATON() to convert the string representation of the IP Address into an unsigned Integer.  INET_NTOA() will perform the reverse translation.

This will result in space savings, and faster queries, who could ask for more than that?

mysql> show processlist;
show processlist;
+—-+————-+—————–+——+———+——+———————————-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+————-+—————–+——+———+——+———————————-+——————+
| 1 | root | localhost:32893 | NULL | Sleep | 0 | | NULL |
| 5 | system user | | NULL | Connect | 98 | Waiting for master to send event | NULL |
| 6 | system user | | NULL | Connect | 5018 | Reading event from the relay log | NULL |
+—–+——+———–+———+———+——-+——-+——————+
3 rows in set (0.00 sec)

What is little known however is that the Show Full Processlist command will display the full query in the Info column.  By default, Show Processlist will only display the first 100 characters. One thing to keep in mind about the Info line is that while the statement (query) might be the one sent to the server, it will display the innermost statement if the statement executes other statements.

SET AUTOCOMMIT = 0;
SET FOREIGN_KEY_CHECKS=0;

.. your dump file ..

SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
SET AUTOCOMMIT = 1;

This will force MySQL to not commit until all rows have been loaded, as well as skip all foreign keys checks. Only skip these checks if you are 100% sure that no constraint is violated. This will usually be the case when dumping from one table and inserting into another.

One situation in which an index on a boolean field (or other low cardinality field) might be useful is if there are relatively few of one of the values, for example 5 True values in a table of millions of records and you are searching for those few values on a regular basis.

However, you might index a boolean value on a combination of fields. Indexing on a single Boolean might be pointless, because there’s only 2 (or 3) values.  However, indexing on 16 boolean values has the potential of  2^16 values.  It might help to make a combined index but you should understand how the combined index can and cannot be used and be aware that the order of the columns matters.

In general, you should always profile your system to see if there are queries that are too slow and consider adding another index to handle those queries. Sometimes a single combined index can be used for multiple queries and others time you will need to make an index for each type of query. Remember that adding indexes slows down modifications to the data so it is possible to have too many indexes. There is always a  trade-off when creating multiple indexes.