Have you ever had a table where you wanted to drop all the information and reset the id field? Say you have the auto_increment value at 1024 and you want to drop all the data and make all new rows start from 1. Or maybe you have the auto_increment value at 1024 and you want to jump it to have all new rows start from 10024. Here is how
1 2 |
ALTER TABLE {table_name} AUTO_INCREMENT = {new_value};
|
Thats it.
By default MySQL only permits connections from the server on which it resides.
Using SSH you need to connect to your server, then need to edit the my.cnf file, generally located in /etc/mysql/my.cnf on Debian/Ubuntu
Under the [mysqld] section alter the “bind-address” line to bind to the public IP address of the server instead of the loop back IP.
1 2 3 4 5 |
[mysqld] datadir=/var/lib/mysql socket=/var/run/mysqld/mysqld.socket bind-address=127.0.0.1 |
Save the file then restart the MySQL service (# /etc/init.d/mysqld restart)
Now using the MySQL admin tool of your choice create your user accounts but specify the host that they will be connecting from. For example if you are creating a user that connects from the IP address 80.65.35.43 the username will be in this format: username@80.65.35.43
I was doing some work where I needed to run some heavy queries on a database using the production data. Naturally I made a backup of the database and the plan was to restore it to my local MySQL instance on my development box and then run my tests. I hit a couple snags, and in an effort to document everything that I run into that requires some gumshoeing here is how I fixed it.
I read a bunch of people saying you could use –max_allowed_packet=100M in the command line but I had no luck with this. I also found some people who said you could edit my.cnf to put the setting there but the location and existence of this file appears to be different per the platform and installation method for MySQL. The end result is to update the setting directly at the mysql prompt. So:
1 2 |
mysql> SET GLOBAL max_allowed_packet = 16 * 1024 * 1024; |