Lik`s forum

[lik]

Administrating a MySQL server

Setting the password:
1. From Unix shell:
shell> mysql -u username -h hostname -p password
mysql> SET PASSWORD FOR 'username'@'localhost'=PASSWORD('new_password');

2. Directly manipulate the privilege tables:
shell> mysql -u username -h host -u username -p
mysql> UPDATE user SET Password=PASSWORD('new_password') WHERE user='root';
mysql> FLUSH PRIVILEGES;

3. Using the mysqladmin command:
shell> mysqladmin -u username password new_password

In our case we were able to change password specifying host name along with user name:
shell> bin/myslqadmin u username h localhost

MySQL Permissions & Grant Tables

In order to add a new user or update user's privileges in mysql grant tables login to mysql as a root user.

There are two options: use GRANT/REVOKE command or manipulating the MySQL grant tables directly.
The preferred method is to use GRANT statements - more concise and less error-prone.

If you modify the grant tables manually (using INSERT, UPDATE, etc.), you should execute
a FLUSH PRIVILEGES statement to tell the server to reload the grant tables.

To remove user: mysql> delete from user where user='username';
mysql> FLUSH PRIVILEGES;

Examples adding a new user with different level of privileges:
dummy: A user who can connect without a password, but only from the local host.

mysql> GRANT USAGE ON *.* TO dummy@localhost;

myUser : A full superuser who can connect to the server from anywhere,
but who must use a password 'pass' to do so.
GRANT statements should be for both myUser@localhost and myUser@"%".
to prevent the anonymous user entry for localhost take precedence.

mysql> GRANT ALL PRIVILEGES ON *.* TO myUser@localhost
IDENTIFIED BY 'pass' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO myUser@"%"
IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

"%" - is a wildcard in mysql. If you are defining your DB table and in the 'host' field
enter '%', that means that any host can access that database (Of course, that host
must also have a valid db user).


admin: A user who can connect from localhost without a password and who is granted
the RELOAD and PROCESS administrative privileges.
No database-related privileges are granted.

mysql> GRANT RELOAD,PROCESS ON *.* TO admin@localhost;


Add a user that has full rights to his database only but cannot see other database:
mysql> GRANT USAGE ON *.* TO 'user'@'host' GRANT Select, Insert, Update, Delete,
Create, Drop ON `database`.* TO 'user'@'host' FLUSH PRIVELEGS;

The FILE privelege and WITH GRANT OPTION may not be the best way to include, it is
only in case of creating another superuser with full set of privileges or
giving privileges to load data using mysql command INLOAD DATA.


GRANT TABLE FIELDS EXPLANATION:
TABLE USER: Everything after "password" is a privelege granted with values 'Y' or 'N'.
This table controls individual user global access rights.

'host','user','password','select','insert','update','delete','index','alter'
,'create','drop','grant','reload','shutdown','process','file'

TABLE DB: This controls access of USERS to databases.

'host','db','user','select','insert','update','delete','index','alter',
'create','drop','grant'

TABLE HOST: This controls which HOSTS are allowed what global access rights.

'host','db','select','insert','update','delete','index','alter',
'create','drop','grant'


HOST, USER, and DB table are very closely connected - if an authorized USER
attempts an SQL request from an unauthorized HOST, it is denied.
If a request from an authorized HOST is not an authorized USER, it is denied.
If a globally authorized USER does not have rights to a certain DB, it is denied.



Backups in MySQL

Full backup of MySql databases:
1. shell> mysqldump --tab=/path/to/some/dir --opt --full
OR
2. shell> mysqlhotcopy database /path/to/some/dir
OR
3. simply copy all table files (`*.frm', `*.MYD', and `*.MYI' files)

For a SQL level backup of a table use SELECT INTO OUTFILE or BACKUP TABLE.

mysql> BACKUP TABLE tbl_name[,tbl_name...] TO '/path/to/backup/directory'
Copies to the backup directory the minimum number of table files needed to
restore the table, after flushing any buffered changes to disk.

RESTORE TABLE tbl_name[,tbl_name...] FROM '/path/to/backup/directory'
Restores the table(s) from the backup that was made with BACKUP TABLE.
Existing tables will not be overwritten; if you try to restore over an existing
table, you will get an error. Restoring will take longer than backing up due to
the need to rebuild the index. The more keys you have, the longer it will take.
Just as BACKUP TABLE, RESTORE TABLE currently works only for MyISAM tables.

Selective backups can be done with:
SELECT * INTO OUTFILE 'file_name' FROM tbl_name
and restore with:
LOAD DATA INFILE 'file_name' REPLACE ...
To avoid duplicate records, you need a PRIMARY KEY or a UNIQUE key in the table.
The REPLACE keyword causes old records to be replaced with new ones when a new
record duplicates an old record on a unique key value.

MyISAM engine tools

The myisamchk utility is used to get information, check, repair or optimise mysql database tables:
shell> myisamchk [options] tbl_name

With no options, myisamchk simply checks the table.

Some useful Options for myisamchk utility:

1. Print informational statistics about the table that is checked: -i or --information
2. Check only tables that have changed since the last check: -C or --check-only-changed
3. The recommended way to quickly check all tables:
myisamchk --silent --fast /path/to/datadir/*/*.MYI



MysqlTuner script
Useful script, that gather and shortly display different mysql settings:
http://tools.assembla.com/svn/mysqltuner/mysqltuner.pl

[lik]

Resetting MySQL root password on Unix

Use the following procedure for resetting the password for any MySQL root accounts on Unix. The instructions assume that you will start the server so that it runs using the Unix login account that you normally use for running the server. For example, if you run the server using the mysql login account, you should log in as mysql before using the instructions. (Alternatively, you can log in as root, but in this case you must start start mysqld with the --user=mysql option. If you start the server as root without using --user=mysql, the server may create root-owned files in the data directory, such as log files, and these may cause permission-related problems for future server startups. If that happens, you will need to either change the ownership of the files to mysql or remove them.)

Log on to your system as the Unix mysql user that the mysqld server runs as.

Locate the .pid file that contains the server's process ID. The exact location and name of this file depend on your distribution, host name, and configuration. Common locations are /var/lib/mysql/, /var/run/mysqld/, and /usr/local/mysql/data/. Generally, the file name has an extension of .pid and begins with either mysqld or your system's host name.

You can stop the MySQL server by sending a normal kill (not kill -9) to the mysqld process, using the path name of the .pid file in the following command:

Code: Select all

shell> kill `cat /mysql-data-directory/host_name.pid`

Note the use of backticks rather than forward quotes with the cat command; these cause the output of cat to be substituted into the kill command.

Create a text file and place the following statements in it. Replace the password with the password that you want to use.

Code: Select all

UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;

The UPDATE and FLUSH statements each must be written on a single line. The UPDATE statement resets the password for all existing root accounts, and the FLUSH statement tells the server to reload the grant tables into memory.

Save the file. For this example, the file will be named /home/me/mysql-init. The file contains the password, so it should not be saved where it can be read by other users.

Start the MySQL server with the special --init-file option:

Code: Select all

shell> mysqld_safe --init-file=/home/me/mysql-init &

The server executes the contents of the file named by the --init-file option at startup, changing each root account password.

After the server has started successfully, delete /home/me/mysql-init.

You should now be able to connect to MySQL as root using the new password.

Alternatively, on any platform, you can set the new password using the mysql client (but this approach is less secure):

Stop mysqld and restart it with the --skip-grant-tables option.

Connect to the mysqld and issue the following statements in the mysql client. Replace the password with the password that you want to use.

Code: Select all

shell> mysql

mysql> UPDATE mysql.user SET Password=PASSWORD('MyNewPass')
    ->                   WHERE User='root';
mysql> FLUSH PRIVILEGES;

You should now be able to connect to MySQL as root using the new password.