Say for example, you have a web page you want to access some data on but the database is located on a different server. To do this you will need to add another user to the mysql.users table and specify the remote location where you are accesing from.
$ mysql -u root -p
mysql> GRANT ALL PRIVILEGES ON database_name.* TO myUser@host_name IDENTIFIED BY 'pass' WITH GRANT OPTION;
*user ‘%’ for hostname if wanting to accept connections from all hosts.
mysql> Flush priviledges
Next, edit your /etc/mysql/my.cnf file:
Comment the bind-address
#bind-address: 127.0.0.1 //if enabled with disallow connections from a remote host.
Restart mysql:
# /etc/init.d/mysql restart
Uncategorized, mysql
mysql
Do a check on the database and verify if there are errrors:
root@psedev2:/elog# mysqlcheck -u root -p jesterads
jester_jesterads.ajax_example OK
jester_jesterads.banners OK
jester_jesterads.category1 OK
jester_jesterads.category2 OK
jester_jesterads.category3 OK
jester_jesterads.event_invites OK
jester_jesterads.event_member_list OK
jester_jesterads.event_reminders OK
jester_jesterads.faq OK
jester_jesterads.groups OK
jester_jesterads.groups_discussions OK
jester_jesterads.inventory OK
jester_jesterads.location_cities OK
jester_jesterads.location_map OK
jester_jesterads.location_neighborhoods OK
jester_jesterads.location_states OK
jester_jesterads.log
warning : Table is marked as crashed
warning : 3 clients are using or haven’t closed the table properly
error : Size of indexfile is: 8814592 Should be: 8815616
error : Corrupt
jester_jesterads.mailing_list OK
jester_jesterads.members OK
# mysql -u jester_root -p jester_jest
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 479671 to server version: 4.1.22-standard-log
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql> check table log;
+———————-+——-+———-+———————————————————-+
| Table | Op | Msg_type | Msg_text |
+———————-+——-+———-+———————————————————-+
| jester_jesterads.log | check | warning | Table is marked as crashed |
| jester_jesterads.log | check | warning | 3 clients are using or haven’t closed the table properly |
| jester_jesterads.log | check | error | Size of indexfile is: 8814592 Should be: 8815616 |
| jester_jesterads.log | check | error | Corrupt |
+———————-+——-+———-+———————————————————-+
4 rows in set (0.00 sec)
mysql> repair table log;
+———————-+——–+———-+———-+
| Table | Op | Msg_type | Msg_text |
+———————-+——–+———-+———-+
| jester_jesterads.log | repair | status | OK |
+———————-+——–+———-+———-+
1 row in set (14.66 sec)
Linux, mysql
mysql
root@donkey:~# /usr/sbin/mysqld --skip-grant-tables
In second term window do this..
root@donkey:~# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.75-0ubuntu10.2 (Ubuntu)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> update mysql.user set Password=PASSWORD('somepass') where User='root';
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
Quit mysql and log in…
root@donkey:~# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.75-0ubuntu10.2 (Ubuntu)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
Linux
mysql