Set - 5

Question 11 :

Setting Up the Initial MySQL Privileges

Answer :

After installing MySQL, you set up the initial access privileges by running scripts/mysql_install_db. The mysql_install_db script starts up the mysqld server, then initializes the grant tables to contain the following set of privileges:

The MySQL root user is created as a superuser who can do anything. Connections must be made from the local host. NOTE: The initial root password is empty, so anyone can connect as root without a password and be granted all privileges. An anonymous user is created that can do anything with databases that have a name of 'test' or starting with 'test_'. Connections must be made from the local host. This means any local user can connect without a password and be treated as the anonymous user.
Other privileges are denied. For example, normal users can't use mysqladmin shutdown or mysqladmin processlist.
NOTE: The default privileges are different for Windows.

Because your installation is initially wide open, one of the first things you should do is specify a password for the MySQL root user. You can do this as follows (note that you specify the password using the PASSWORD() function):

shell> mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('new_password')
WHERE user='root';
mysql> FLUSH PRIVILEGES;

You can, in MySQL Version 3.22 and above, use the SET PASSWORD statement:

shell> mysql -u root mysql
mysql> SET PASSWORD FOR root=PASSWORD('new_password');

Another way to set the password is by using the mysqladmin command:

shell> mysqladmin -u root password new_password

Only users with write/update access to the mysql database can change the password for others users. All normal users (not anonymous ones) can only change their own password with either of the above commands or with SET PASSWORD=PASSWORD('new password').

Note that if you update the password in the user table directly using the first method, you must tell the server to re-read the grant tables (with FLUSH PRIVILEGES), because the change will go unnoticed otherwise.

Once the root password has been set, thereafter you must supply that password when you connect to the server as root.

You may wish to leave the root password blank so that you don't need to specify it while you perform additional setup or testing. However, be sure to set it before using your installation for any real production work.

See the scripts/mysql_install_db script to see how it sets up the default privileges. You can use this as a basis to see how to add other users.

If you want the initial privileges to be different than those just described above, you can modify mysql_install_db before you run it.

To re-create the grant tables completely, remove all the `.frm', `.MYI', and `.MYD' files in the directory containing the mysql database. (This is the directory named `mysql' under the database directory, which is listed when you run mysqld --help.) Then run the mysql_install_db script, possibly after editing it first to have the privileges you want.

NOTE: For MySQL versions older than Version 3.22.10, you should NOT delete the `.frm' files. If you accidentally do this, you should copy them back from your MySQL distribution before running mysql_install_db.


Question 12 :

Adding New User Privileges to MySQL

Answer :

ou can add users two different ways: by using GRANT statements or by manipulating the MySQL grant tables directly. The preferred method is to use GRANT statements, because they are more concise and less error-prone.

The examples below show how to use the mysql client to set up new users. These examples assume that privileges are set up according to the defaults described in the previous section. This means that to make changes, you must be on the same machine where mysqld is running, you must connect as the MySQL root user, and the root user must have the insert privilege for the mysql database and the reload administrative privilege. Also, if you have changed the root user password, you must specify it for the mysql commands below.

You can add new users by issuing GRANT statements:

shell> mysql --user=root mysql
mysqlgt; GRANT ALL PRIVILEGES ON *.* TO monty@localhost
IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysqlgt; GRANT ALL PRIVILEGES ON *.* TO monty@"%"
IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysqlgt; GRANT RELOAD,PROCESS ON *.* TO admin@localhost;
mysqlgt; GRANT USAGE ON *.* TO dummy@localhost;

These GRANT statements set up three new users:

monty
A full superuser who can connect to the server from anywhere, but who must use a password 'some_pass' to do so. Note that we must issue GRANT statements for both monty@localhost and monty@"%". If we don't add the entry with localhost, the anonymous user entry for localhost that is created by mysql_install_db will take precedence when we connect from the local host, because it has a more specific Host field value and thus comes earlier in the user table sort order.
admin
A user who can connect from localhost without a password and who is granted the reload and process administrative privileges. This allows the user to execute the mysqladmin reload, mysqladmin refresh, and mysqladmin flush-* commands, as well as mysqladmin process list . No database-related privileges are granted. (They can be granted later by issuing additional GRANT statements.)
dummy
A user who can connect without a password, but only from the local host. The global privileges are all set to 'N' -- the USAGE privilege type allows you to create a user with no privileges. It is assumed that you will grant database-specific privileges later.
You can also add the same user access information directly by issuing INSERT statements and then telling the server to reload the grant tables:

shellgt; mysql --user=root mysql
mysqlgt; INSERT INTO user VALUES('localhost','monty',PASSWORD('some_pass'),
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y')
mysqlgt; INSERT INTO user VALUES('%','monty',PASSWORD('some_pass'),
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y')
mysqlgt; INSERT INTO user SET Host='localhost',User='admin',
Reload_priv='Y', Process_priv='Y';
mysqlgt; INSERT INTO user (Host,User,Password)
VALUES('localhost','dummy','');
mysqlgt; FLUSH PRIVILEGES;

Depending on your MySQL version, you may have to use a different number of 'Y' values above (versions prior to Version 3.22.11 had fewer privilege columns). For the admin user, the more readable extended INSERT syntax that is available starting with Version 3.22.11 is used.

Note that to set up a superuser, you need only create a user table entry with the privilege fields set to 'Y'. No db or host table entries are necessary.

The privilege columns in the user table were not set explicitly in the last INSERT statement (for the dummy user), so those columns are assigned the default value of 'N'. This is the same thing that GRANT USAGE does.

The following example adds a user custom who can connect from hosts localhost, server.domain, and whitehouse.gov. He wants to access the bankaccount database only from localhost, the expenses database only from whitehouse.gov, and the customer database from all three hosts. He wants to use the password stupid from all three hosts.

To set up this user's privileges using GRANT statements, run these commands:

shellgt; mysql --user=root mysql
mysqlgt; GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON bankaccount.*
TO custom@localhost
IDENTIFIED BY 'stupid';
mysqlgt; GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON expenses.*
TO custom@whitehouse.gov
IDENTIFIED BY 'stupid';
mysqlgt; GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON customer.*
TO custom@'%'
IDENTIFIED BY 'stupid';

To set up the user's privileges by modifying the grant tables directly, run these commands (note the FLUSH PRIVILEGES at the end):

shellgt; mysql --user=root mysql
mysqlgt; INSERT INTO user (Host,User,Password)
VALUES('localhost','custom',PASSWORD('stupid'));
mysqlgt; INSERT INTO user (Host,User,Password)
VALUES('server.domain','custom',PASSWORD('stupid'));
mysqlgt; INSERT INTO user (Host,User,Password)
VALUES('whitehouse.gov','custom',PASSWORD('stupid'));
mysqlgt; INSERT INTO db
(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
Create_priv,Drop_priv)
VALUES
('localhost','bankaccount','custom','Y','Y','Y','Y','Y','Y');
mysqlgt; INSERT INTO db
(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
Create_priv,Drop_priv)
VALUES
('whitehouse.gov','expenses','custom','Y','Y','Y','Y','Y','Y');
mysqlgt; INSERT INTO db
(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
Create_priv,Drop_priv)
VALUES('%','customer','custom','Y','Y','Y','Y','Y','Y');
mysqlgt; FLUSH PRIVILEGES;

The first three INSERT statements add user table entries that allow user custom to connect from the various hosts with the given password, but grant no permissions to him (all privileges are set to the default value of 'N'). The next three INSERT statements add db table entries that grant privileges to custom for the bankaccount, expenses, and customer databases, but only when accessed from the proper hosts. As usual, when the grant tables are modified directly, the server must be told to reload them (with FLUSH PRIVILEGES) so that the privilege changes take effect.

If you want to give a specific user access from any machine in a given domain, you can issue a GRANT statement like the following:

mysql> GRANT ...
ON *.*
TO myusername@"%.mydomainname.com"
IDENTIFIED BY 'mypassword';

To do the same thing by modifying the grant tables directly, do this:

mysqlgt; INSERT INTO user VALUES ('%.mydomainname.com', 'myusername',
PASSWORD('mypassword'),...);
mysqlgt; FLUSH PRIVILEGES;

You can also use xmysqladmin, mysql_webadmin, and even xmysql to insert, change, and update values in the grant tables. You can find these utilities in the Contrib directory of the MySQL Website.


Question 13 :

MySQL - Setting Up Passwords

Answer :

In most cases you should use GRANT to set up your users/passwords, so the following only applies for advanced users.
The examples in the preceding sections illustrate an important principle: when you store a non-empty password using INSERT or UPDATE statements, you must use the PASSWORD() function to encrypt it. This is because the user table stores passwords in encrypted form, not as plaintext. If you forget that fact, you are likely to attempt to set passwords like this:

shell> mysql -u root mysql
mysql> INSERT INTO user (Host,User,Password)
VALUES('%','jeffrey','biscuit');
mysql> FLUSH PRIVILEGES;

The result is that the plaintext value 'biscuit' is stored as the password in the user table. When the user jeffrey attempts to connect to the server using this password, the mysql client encrypts it with PASSWORD() and sends the result to the server. The server compares the value in the user table (the encrypted value of 'biscuit') to the encrypted password (which is not 'biscuit'). The comparison fails and the server rejects the connection:

shell> mysql -u jeffrey -pbiscuit test
Access denied

Passwords must be encrypted when they are inserted in the user table, so the INSERT statement should have been specified like this instead:

mysql> INSERT INTO user (Host,User,Password)
VALUES('%','jeffrey',PASSWORD('biscuit'));

You must also use the PASSWORD() function when you use SET PASSWORD statements:

mysql> SET PASSWORD FOR jeffrey@"%" = PASSWORD('biscuit');

If you set passwords using the GRANT ... IDENTIFIED BY statement or the mysqladmin password command, the PASSWORD() function is unnecessary. They both take care of encrypting the password for you, so you would specify a password of 'biscuit' like this:

mysql> GRANT USAGE ON *.* TO jeffrey@"%" IDENTIFIED BY 'biscuit';

or

shell> mysqladmin -u jeffrey password biscuit

NOTE: PASSWORD() does not perform password encryption in the same way that Unix passwords are encrypted. You should not assume that if your Unix password and your MySQL password are the same, that PASSWORD() will result in the same encrypted value as is stored in the Unix password file.


Question 14 :

MySQL - Causes of Access denied Errors

Answer :

If you encounter Access denied errors when you try to connect to the MySQL server, the list below indicates some courses of action you can take to correct the problem:

The server should let you connect without error. You should also make sure you have a file `user.MYD' in the MySQL database directory. Ordinarily, this is `PATH/var/mysql/user.MYD', where PATH is the pathname to the MySQL installation root.
After a fresh installation, you should connect to the server and set up your users and their access permissions:
shell> mysql -u root mysql

The server should let you connect because the MySQL root user has no password initially. That is also a security risk, so setting the root password is something you should do while you're setting up your other MySQL users. If you try to connect as root and get this error:
Access denied for user: '@unknown' to database mysql

this means that you don't have an entry in the user table with a User column value of 'root' and that mysqld cannot resolve the hostname for your client. In this case, you must restart the server with the --skip-grant-tables option and edit your `/etc/hosts' or `\windows\hosts' file to add an entry for your host.
If you updated an existing MySQL installation from a version earlier than Version 3.22.11 to Version 3.22.11 or later, did you run the mysql_fix_privilege_tables script? If not, do so. The structure of the grant tables changed with MySQL Version 3.22.11 when the GRANT statement became functional.

If you can't get your password to work, remember that you must use the PASSWORD() function if you set the password with the INSERT, UPDATE, or SET PASSWORD statements. The PASSWORD() function is unnecessary if you specify the password using the GRANT ... INDENTIFIED BY statement or the mysqladmin password command.
localhost is a synonym for your local hostname, and is also the default host to which clients try to connect if you specify no host explicitly. However, connections to localhost do not work if you are running on a system that uses MIT-pthreads (localhost connections are made using Unix sockets, which are not supported by MIT-pthreads). To avoid this problem on such systems, you should use the --host option to name the server host explicitly. This will make a TCP/IP connection to the mysqld server. In this case, you must have your real hostname in user table entries on the server host. (This is true even if you are running a client program on the same host as the server.)
If you get an Access denied error when trying to connect to the database with mysql -u user_name db_name, you may have a problem with the user table. Check this by executing mysql -u root mysql and issuing this SQL statement:
mysql> SELECT * FROM user;

The result should include an entry with the Host and User columns matching your computer's hostname and your MySQL user name.
The Access denied error message will tell you who you are trying to log in as, the host from which you are trying to connect, and whether or not you were using a password. Normally, you should have one entry in the user table that exactly matches the hostname and user name that were given in the error message. For example if you get an error message that contains Using password: NO, this means that you tried to login without an password.
If you get the following error when you try to connect from a different host than the one on which the MySQL server is running, then there is no row in the user table that matches that host:
Host ... is not allowed to connect to this MySQL server

You can fix this by using the command-line tool mysql (on the server host!) to add a row to the user, db, or host table for the user/hostname combination from which you are trying to connect and then execute mysqladmin flush-privileges. If you are not running MySQL Version 3.22 and you don't know the IP number or hostname of the machine from which you are connecting, you should put an entry with '%' as the Host column value in the user table and restart mysqld with the --log option on the server machine. After trying to connect from the client machine, the information in the MySQL log will indicate how you really did connect. (Then replace the '%' in the user table entry with the actual hostname that shows up in the log. Otherwise, you'll have a system that is insecure.) Another reason for this error on Linux is that you are using a binary MySQL version that is compiled with a different glibc version than the one you are using. In this case you should either upgrade your OS/glibc or download the source MySQL version and compile this yourself. A source RPM is normally trivial to compile and install, so this isn't a big problem.
If you get an error message where the hostname is not shown or where the hostname is an IP, even if you try to connect with a hostname:
shell> mysqladmin -u root -pxxxx -h some-hostname ver
Access denied for user: 'root' (Using password: YES)

This means that MySQL got some error when trying to resolve the IP to a hostname. In this case you can execute mysqladmin flush-hosts to reset the internal DNS cache. Some permanent solutions are:
Try to find out what is wrong with your DNS server and fix this.
Specify IPs instead of hostnames in the MySQL privilege tables.
Start mysqld with --skip-name-resolve.
Start mysqld with --skip-host-cache.
Connect to localhost if you are running the server and the client on the same machine.
Put the client machine names in /etc/hosts.
If mysql -u root test works but mysql -h your_hostname -u root test results in Access denied, then you may not have the correct name for your host in the user table. A common problem here is that the Host value in the user table entry specifies an unqualified hostname, but your system's name resolution routines return a fully qualified domain name (or vice-versa). For example, if you have an entry with host 'tcx' in the user table, but your DNS tells MySQL that your hostname is 'tcx.subnet.se', the entry will not work. Try adding an entry to the user table that contains the IP number of your host as the Host column value. (Alternatively, you could add an entry to the user table with a Host value that contains a wild card--for example, 'tcx.%'. However, use of hostnames ending with `%' is insecure and is not recommended!) If mysql -u user_name test works but mysql -u user_name other_db_name doesn't work, you don't have an entry for other_db_name listed in the db table.
If mysql -u user_name db_name works when executed on the server machine, but mysql -u host_name -u user_name db_name doesn't work when executed on another client machine, you don't have the client machine listed in the user table or the db table. If you can't figure out why you get Access denied, remove from the user table all entries that have Host values containing wild cards (entries that contain `%' or `_'). A very common error is to insert a new entry with Host='%' and User='some user', thinking that this will allow you to specify localhost to connect from the same machine. The reason that this doesn't work is that the default privileges include an entry with Host='localhost' and User=''. Because that entry has a Host value 'localhost' that is more specific than '%', it is used in preference to the new entry when connecting from localhost! The correct procedure is to insert a second entry with Host='localhost' and User='some_user', or to remove the entry with Host='localhost' and User=''.
If you get the following error, you may have a problem with the db or host table:
Access to database denied

If the entry selected from the db table has an empty value in the Host column, make sure there are one or more corresponding entries in the host table specifying which hosts the db table entry applies to. If you get the error when using the SQL commands SELECT ... INTO OUTFILE or LOAD DATA INFILE, your entry in the user table probably doesn't have the file privilege enabled.
Remember that client programs will use connection parameters specified in configuration files or environment variables. If a client seems to be sending the wrong default connection parameters when you don't specify them on the command line, check your environment and the `.my.cnf' file in your home directory. You might also check the system-wide MySQL configuration files, though it is far less likely that client connection parameters will be specified there. If you get Access denied when you run a client without any options, make sure you haven't specified an old password in any of your option files!

If you make changes to the grant tables directly (using an INSERT or UPDATE statement) and your changes seem to be ignored, remember that you must issue a FLUSH PRIVILEGES statement or execute a mysqladmin flush-privileges command to cause the server to re-read the privilege tables. Otherwise your changes have no effect until the next time the server is restarted. Remember that after you set the root password with an UPDATE command, you won't need to specify it until after you flush the privileges, because the server won't know you've changed the password yet!
If you have access problems with a Perl, PHP, Python, or ODBC program, try to connect to the server with mysql -u user_name db_name or mysql -u user_name -pyour_pass db_name. If you are able to connect using the mysql client, there is a problem with your program and not with the access privileges. (Note that there is no space between -p and the password; you can also use the --password=your_pass syntax to specify the password. If you use the -p option alone, MySQL will prompt you for the password.)
For testing, start the mysqld daemon with the --skip-grant-tables option. Then you can change the MySQL grant tables and use the mysqlaccess script to check whether or not your modifications have the desired effect. When you are satisfied with your changes, execute mysqladmin flush-privileges to tell the mysqld server to start using the new grant tables. Note: Reloading the grant tables overrides the --skip-grant-tables option. This allows you to tell the server to begin using the grant tables again without bringing it down and restarting it.
If everything else fails, start the mysqld daemon with a debugging option (for example, --debug=d,general,query). This will print host and user information about attempted connections, as well as information about each command issued.

If you have any other problems with the MySQL grant tables and feel you must post the problem to the mailing list, always provide a dump of the MySQL grant tables. You can dump the tables with the mysqldump mysql command. As always, post your problem using the mysqlbug script.


Question 15 :

Replication in MySQL

Answer :

One way replication can be used is to increase both robustness and speed. For robustness you can have two systems and can switch to the backup if you have problems with the master. The extra speed is achieved by sending a part of the non-updating queries to the replica server. Of course this only works if non-updating queries dominate, but that is the normal case.

Starting in Version 3.23.15, MySQL supports one-way replication internally. One server acts as the master, while the other acts as the slave. Note that one server could play the roles of master in one pair and slave in the other. The master server keeps a binary log of updates and an index file to binary logs to keep track of log rotation. The slave, upon connecting, informs the master where it left off since the last successfully propagated update, catches up on the updates, and then blocks and waits for the master to notify it of the new updates.

Note that if you are replicating a database, all updates to this database should be done through the master!

On older servers one can use the update log to do simple replication.

Another benefit of using replication is that one can get live backups of the system by doing a backup on a slave instead of doing it on the master.