Finding MySQL server operating parameters
If you have a MySQL server listening on a given port number, you can use the following command to find out what operating parameters it is using for several important configurable variables, including the base directory and Unix socket file name:
# mysqladmin –host=host_name –port=port_number variables
With the information displayed by that command, you can tell what option values not to use when configuring an additional server.
Exporting and Importing mysql databases
Exporting the Database.
In this example, we will export the ‘mydb’ database into a file called ‘mydb.sql’:
mysqldump -u root -p mydb > mydb.sql
Importing the data is just as easy but involves two steps.
The first step is to create a blank database ready to receive the data.
mysqladmin -u root -p create mydb1
Once done, all that is left is to actually import the data:
mysql -u root -p mydb1 < mydb.sql
MySQL database does not work and returns “Can’t create/write to file” error
The problem is related to the mysql directory permission.
‘mysql’ user is not able to create a temporary file in /var/lib/mysql/ directory due to permissions lack
# ls -lda ~mysql/
drwxr-xr-x 5 root mysql 4096 Aug 31 09:56 /var/lib/mysql/
The solution is to go to /etc/init.d/mysqld_app_init file and change
chown root.mysql /var/lib/mysql
to
chown mysql.mysql /var/lib/mysql
Then restart mysqld service or just change owner of /var/lib/mysql directory to “mysql” instead of “root”
chown root /var/lib/mysql
How to check privileges for an mysql account.
To check the privileges for an account, use SHOW GRANTS:
mysql> SHOW GRANTS FOR ‘root’@'localhost’;
+—————————————————-+
| Grants for root@localhost
+—————————————————-+
| GRANT ALL PRIVILEGES ON *.* TO ‘root’@'localhost’ IDENTIFIED BY PASSWORD ‘*C406D12D7025EFA560629ABD992F09C9E28002C6′ WITH GRANT OPTION |
+—————————————————-+
1 row in set (0.00 sec)
mysql>
How to enable access from all machines for mysql user.
To create a user who has access from all machines in a given domain (for example, serverbuddies.com), you can use the “%” wildcard character in the host part of the account name:
mysql> CREATE USER ‘myname’@'%.serverbuddies.com’ IDENTIFIED BY ‘mypass’;
To do the same thing by modifying the grant tables directly, do this:
mysql> INSERT INTO user (Host,User,Password,…)
-> VALUES(’%.serverbuddies.com’,'myname’,PASSWORD(’mypass’),…);
mysql> FLUSH PRIVILEGES;
