Last Updated: Sunday 17th April 2005 11:02, AHD ******************************************************* Saturday 26th February, 16:44 PT, AHD My Linux system is a full installation of Red Hat Fedora Core 3 which includes MySQL and Apache web server. As root, from /etc/init.d I started MySQL with the following command: # /etc/rc.d/init.d/mysqld start Linux immediately responded with: Starting MySQL: [ OK ] Still in: /etc/init.d I now tested MySQL: # mysqladmin version This gives me all kinds of info on my version of MySQL, so I'm assuming all is ok. Now I create a password for the SuperUser: As root, from /etc/init.d # mysql -u root -p It then prompts for a password: Enter password: it didn't accept my password ???? will continue tomorrow... (after further delving in the documentation on Sunday, I realised that after the installation, there is no root password, so entering one would be the same as entering an invalid password!) Sunday 27th February, 9:04 PT, AHD password problem: found this on the net: *************************************************************************** From the net: > I have a problem, It's been months since I used MySQL and (I believe) I had > set it up with a root password. Now I can't log on to MySQL as root MySQL > user and create a new user or manage an existing user (I can log onto server Familiar situation. :) Do so: service mysql stop wait until MySQL shuts down. Then run mysqld_safe --skip-grant-tables & then you will be able to login as root with no password. mysql -uroot mysql In MySQL command line prompt issue the following command: UPDATE user SET password=PASSWORD("abcd") WHERE user="root"; FLUSH PRIVILEGES; At this time your root password is reset to "abcd" and MySQL will now know the privileges and you'll be able to login with your new password: mysql -uroot -pabcd mysql end of from the net ********************************************************************************** # cd /etc/init.d # service mysql stop that didn't work trying this: # /etc/rc.d/init.d/mysqld stop this produced: Stopping MySQL: [ OK ] Still in: /etc/init.d Checked no MySQL processes were running using: ps waux | grep mysql none were running. Now need to run safe_mysqld: # cd /usr/bin # mysqld_safe --skip-grant-tables & responded with: [1] 32765 [root@localhost bin]# bash: mysqld_safe: command not found [1]+ Exit 127 mysqld_safe --skip-grant-tables try this: # mysqld_safe --skip-grant-tables results in bash: command not found so try: # /usr/bin/mysqld_safe --skip-grant-tables # no such file or directory so try from: /etc/init.d trying this: # /etc/rc.d/init.d/mysqld_safe --skip-grant-tables # no such file or directory trying this: # /etc/rc.d/init.d/mysqld start --skip-grant-tables this resulted in: Starting MySQL: [ OK ] now trying: then you will be able to login as root with no password. mysql -uroot mysql This was successful and resulted in: "Reading table information for completion of table and column names" "Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version 3.23.58 Type 'help:' or '\h' for help. Type ''\c' to clear the buffer." Now I attempt to set the root MySQL password.... In MySQL command line prompt (mysql>) issued the following command: UPDATE user SET password=PASSWORD("abcd") WHERE user="root"; This responded with: Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 In MySQL command line prompt (mysql>) issued the following command: FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec) I have to flsh privileges to force this update otherwise it would only update after a reboot of the system. In MySQL command line prompt (mysql>) issued the following command: exit; Goes back to root prompt in \etc\init.d Now, at this time my root password is reset to "abcd" and MySQL will now know the privileges and I'll be able to login with my new password: mysql -uroot -pabcd mysql responded with: "Reading table information for completion of table and column names" "Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version 3.23.58 Type 'help:' or '\h' for help. Type ''\c' to clear the buffer." so now I will stop msql and restart, and login as root to double check all is ok... # pwd /etc/init.d # /etc/rc.d/init.d/mysqld stop this produced: Stopping MySQL: [ OK ] Still in: /etc/init.d # /etc/rc.d/init.d/mysqld start this produced: Starting MySQL: [ OK ] # mysql -u root -p Enter password: entered abcd Responds with the Welcome to the MySQL monitor... mysql> exit; See: http://dev.mysql.com/doc/mysql/en/linux-rpm.html 2.4. Installing MySQL on Linux Many Linux distributions still ship with MySQL 3.23 and they usually link applications dynamically to save disk space. If these shared libraries are in a separate package (for example, MySQL-shared), it is sufficient to simply leave this package installed and just upgrade the MySQL server and client packages (which are statically linked and do not depend on the shared libraries). For distributions that include the shared libraries in the same package as the MySQL server (for example, Red Hat Linux), you could either install our 3.23 MySQL-shared RPM, or use the MySQL-shared-compat package instead. The following RPM packages are available... (see the web page for them) what do we need to install???? ******************************************************************* http://dev.mysql.com/doc/mysql/en/post-installation.html Posted by Larry Prall on July 11 2003 7:28am [Delete] [Edit] If you are running RedHat Linux (not sure which versions) you may run into a problem with the mysql-test/mysql-test-run script unless you have your locale configured to POSIX. The other locales seem to sort "repair_part2" before "repair" and that causes repair_part2 to fail. Be sure to set your LANG environment variable to C or POSIX before running the tests. Posted by David on December 23 2003 9:26am [Delete] [Edit] With Fedora Core 1 (and probably RH9), it isn't necessary to run mysql_install_db yourself. To start mysqld, use "/etc/rc.d/init.d/mysqld start". The first time this is run, the database is initialized (with mysql_install_db), and the ownership of /var/lib/mysql/ is set to mysql:mysql. You need to run chkconfig or redhat-config-services to have mysqld started on bootup. ******************************************************************* http://dev.mysql.com/doc/mysql/en/default-privileges.html 2.9.3. Securing the Initial MySQL Accounts * On Unix, the grant tables are populated by the mysql_install_db program. Some installation methods run this program for you. Others require that you execute it manually. For details, see Section 2.9.2, “Unix Post-Installation Procedures”. The grant tables define the initial MySQL user accounts and their access privileges. These accounts are set up as follows: * Two accounts are created with a username of root. These are superuser accounts that can do anything. The initial root account passwords are empty, so anyone can connect to the MySQL server as root without a password and be granted all privileges. On Unix, both root accounts are for connections from the local host. Connections must be made from the local host by specifying a hostname of localhost for one account, or the actual hostname or IP number for the other. Two anonymous-user accounts are created, each with an empty username. The anonymous accounts have no passwords, so anyone can use them to connect to the MySQL server. On Unix, both anonymous accounts are for connections from the local host. Connections must be made from the local host by specifying a hostname of localhost for one account, or the actual hostname or IP number for the other. These accounts have all privileges for the test database or other databases with names that start with test_. As noted, none of the initial accounts have passwords. This means that your MySQL installation is unprotected until you do something about it: * If you want to prevent clients from connecting as anonymous users without a password, you should either assign passwords to the anonymous accounts or else remove them. * You should assign passwords to the MySQL root accounts. The following instructions describe how to set up passwords for the initial MySQL accounts, first for the anonymous accounts and then for the root accounts. Replace ``newpwd'' in the examples with the actual password that you want to use. The instructions also cover how to remove the anonymous accounts, should you prefer not to allow anonymous access at all. You might want to defer setting the passwords until later, so that you don't need to specify them while you perform additional setup or testing. However, be sure to set them before using your installation for any real production work. To assign passwords to the anonymous accounts, you can use either SET PASSWORD or UPDATE. In both cases, be sure to encrypt the password using the PASSWORD() function. To use SET PASSWORD on Unix, do this: shell> mysql -u root mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('newpwd'); mysql> SET PASSWORD FOR ''@'host_name' = PASSWORD('newpwd'); In the second SET PASSWORD statement, replace host_name with the name of the server host. This is the name that is specified in the Host column of the non-localhost record for root in the user table. If you don't know what hostname this is, issue the following statement before using SET PASSWORD: mysql> SELECT Host, User FROM mysql.user; This results in: Host User ---------------------------- localhost localhost root localhost.localdomain localhost.localdomain root Look for the record that has root in the User column and something other than localhost in the Host column. Then use that Host value in the second SET PASSWORD statement. The other way to assign passwords to the anonymous accounts is by using UPDATE to modify the user table directly. Connect to the server as root and issue an UPDATE statement that assigns a value to the Password column of the appropriate user table records. The procedure is the same for Windows and Unix. The following UPDATE statement assigns a password to both anonymous accounts at once: shell> mysql -u root mysql> UPDATE mysql.user SET Password = PASSWORD('newpwd') -> WHERE User = ''; mysql> FLUSH PRIVILEGES; After you update the passwords in the user table directly using UPDATE, you must tell the server to re-read the grant tables with FLUSH PRIVILEGES. Otherwise, the change goes unnoticed until you restart the server. If you prefer to remove the anonymous accounts instead, do so as follows: shell> mysql -u root mysql> DELETE FROM mysql.user WHERE User = ''; mysql> FLUSH PRIVILEGES; The DELETE statement applies both to Windows and to Unix. On Windows, if you want to remove only the anonymous account that has the same privileges as root, do this instead: shell> mysql -u root mysql> DELETE FROM mysql.user WHERE Host='localhost' AND User=''; mysql> FLUSH PRIVILEGES; This account allows anonymous access but has full privileges, so removing it improves security. You can assign passwords to the root accounts in several ways. The following discussion demonstrates three methods: * Use the SET PASSWORD statement * Use the mysqladmin command-line client program * Use the UPDATE statement To assign passwords using SET PASSWORD, connect to the server as root and issue two SET PASSWORD statements. Be sure to encrypt the password using the PASSWORD() function. For Unix, do this: shell> mysql -u root mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd'); mysql> SET PASSWORD FOR 'root'@'host_name' = PASSWORD('newpwd'); In the second SET PASSWORD statement, replace host_name with the name of the server host. This is the same hostname that you used when you assigned the anonymous account passwords. To assign passwords to the root accounts using mysqladmin, execute the following commands: shell> mysqladmin -u root password "newpwd" shell> mysqladmin -u root -h host_name password "newpwd" These commands apply both to Windows and to Unix. In the second command, replace host_name with the name of the server host. The double quotes around the password are not always necessary, but you should use them if the password contains spaces or other characters that are special to your command interpreter. You can also use UPDATE to modify the user table directly. The following UPDATE statement assigns a password to both root accounts at once: shell> mysql -u root mysql> UPDATE mysql.user SET Password = PASSWORD('newpwd') -> WHERE User = 'root'; mysql> FLUSH PRIVILEGES; The UPDATE statement applies both to Windows and to Unix. After the passwords have been set, you must supply the appropriate password whenever you connect to the server. For example, if you want to use mysqladmin to shut down the server, you can do so using this command: shell> mysqladmin -u root -p shutdown Enter password: (enter root password here) ***************************************************************** ***************************************************************** So, as at 16:10PT on Sunday 27th February 2005, the MySQL on my home Linux box has 2 root users and 2 anonymous users. Only the password for root was set at abcd. The others left as is: mysql> SELECT Host, User FROM mysql.user; Host User ---------------------------- localhost localhost root localhost.localdomain localhost.localdomain root ***************************************************************** ***************************************************************** Sunday 17th April 2005 9:22 PT, AHD See also in Koha.txt where MySQL was accessed again from within the Koha installation. Also in here: http://www.kohadocs.org/Installing_Koha_on_Fedora.html#d0e538 I note again the SELinux may conflict with MySQL and Apache causing MySQL not to be able to start and Apache not being able to execute CGI scripts. I actually disabled SELinux, and seemingly have no probs now, but may enable later by following instructions here: http://www.kohadocs.org/Installing_Koha_on_Fedora.html#d0e538 in Section 3.3 Also in the same html under MySQL it talks about how to test MySQL is running ok by running: /usr/share/sql-bench/run-all-tests (full path needed for execution - see below for run and result) and http://www.novell.com/products/linuxpackages/professional/mysql-bench.html: To run these database benchmarks, start the script "run-all-tests" in the directory /usr/share/sql-bench after starting MySQL. See Koha.txt for more info on MySQL with Koha installation with SELinux. As at Sunday 17th April 2005 10:04 # mysql -uroot -pabcd mysql mysql> select host,user from mysql.user; host user ------------------------------ localhost localhost CCLIBSU // see Koha.txt localhost koha // see above localhost root localhost.localdomain localhost.localdomain root 6 rows To test MySQL: make sure MySQL has been started, then: /usr/share/sql-bench/run-all-tests --user='root' --password='password' (where password is MySQL password for the root MySQL user) (--help for help) this runs ok - but it's slow so be patient :-)) (actually took about 2 hours!) [root@localhost sql-bench]# /usr/share/sql-bench/run-all-tests --user='root' --password='****' (substitute *** for actual password) Benchmark DBD suite: 2.14 Date of test: 2005-04-17 10:32:44 Running tests on: Linux 2.6.10-1.770_FC3 i686 Arguments: Comments: Limits from: Server version: MySQL 3.23.58 alter-table: Total time: 250 wallclock secs ( 0.46 usr 0.10 sys + 0.00 cusr 0.00 csys = 0.56 CPU) ATIS: Total time: 49 wallclock secs (22.92 usr 2.49 sys + 0.00 cusr 0.00 csys = 25.41 CPU) big-tables: Total time: 34 wallclock secs (10.47 usr 5.09 sys + 0.00 cusr 0.00 csys = 15.56 CPU) connect: Total time: 126 wallclock secs (71.02 usr 10.23 sys + 0.00 cusr 0.00 csys = 81.25 CPU) create: Total time: 113 wallclock secs (15.13 usr 2.14 sys + 0.00 cusr 0.00 csys = 17.27 CPU) insert: Total time: 4942 wallclock secs (1281.75 usr 133.83 sys + 0.00 cusr 0.00 csys = 1415.58 CPU) select: Total time: 1162 wallclock secs (138.04 usr 12.25 sys + 0.00 cusr 0.00 csys = 150.29 CPU) wisconsin: Total time: 17 wallclock secs ( 6.75 usr 1.13 sys + 0.00 cusr 0.00 csys = 7.88 CPU) All 8 test executed successfully Totals per operation: Operation seconds usr sys cpu tests alter_table_add 133.00 0.26 0.06 0.32 992 alter_table_drop 107.00 0.14 0.02 0.16 496 connect 22.00 14.66 1.35 16.01 10000 connect+select_1_row 26.00 16.09 1.69 17.78 10000 connect+select_simple 24.00 15.52 1.68 17.20 10000 count 30.00 0.08 0.00 0.08 100 count_distinct 41.00 0.73 0.06 0.79 1000 count_distinct_2 49.00 0.84 0.07 0.91 1000 count_distinct_big 89.00 20.79 2.30 23.09 120 count_distinct_group 64.00 2.85 0.26 3.11 1000 count_distinct_group_on_key 37.00 0.85 0.07 0.92 1000 count_distinct_group_on_key_parts 63.00 2.84 0.26 3.10 1000 count_distinct_key_prefix 34.00 0.75 0.06 0.81 1000 count_group_on_key_parts 35.00 2.70 0.27 2.97 1000 count_on_key 325.00 30.54 2.20 32.74 50100 create+drop 19.00 4.22 0.62 4.84 10000 create_MANY_tables 22.00 3.61 0.36 3.97 10000 create_index 5.00 0.00 0.00 0.00 8 create_key+drop 22.00 4.90 0.55 5.45 10000 create_table 0.00 0.01 0.00 0.01 31 delete_all 19.00 0.00 0.00 0.00 12 delete_all_many_keys 451.00 0.02 0.01 0.03 1 delete_big 0.00 0.00 0.00 0.00 1 delete_big_many_keys 451.00 0.02 0.01 0.03 128 delete_key 7.00 0.74 0.20 0.94 10000 drop_index 4.00 0.00 0.00 0.00 8 drop_table 0.00 0.01 0.00 0.01 28 drop_table_when_MANY_tables 16.00 0.90 0.22 1.12 10000 insert 128.00 27.60 7.26 34.86 350768 insert_duplicates 30.00 7.48 1.96 9.44 100000 insert_key 1113.00 21.36 3.53 24.89 100000 insert_many_fields 10.00 0.86 0.07 0.93 2000 insert_select_1_key 4.00 0.00 0.00 0.00 1 insert_select_2_keys 9.00 0.00 0.00 0.00 1 min_max 23.00 0.05 0.00 0.05 60 min_max_on_key 165.00 44.02 3.51 47.53 85000 multiple_value_insert 6.00 0.91 0.03 0.94 100000 order_by_big 95.00 62.05 7.05 69.10 10 order_by_big_key 82.00 63.34 7.46 70.80 10 order_by_big_key2 77.00 61.85 7.29 69.14 10 order_by_big_key_desc 83.00 63.38 7.43 70.81 10 order_by_big_key_diff 92.00 61.95 7.08 69.03 10 order_by_big_key_prefix 79.00 61.85 7.20 69.05 10 order_by_key2_diff 8.00 5.54 0.58 6.12 500 order_by_key_prefix 4.00 2.95 0.32 3.27 500 order_by_range 7.00 3.03 0.35 3.38 500 outer_join 44.00 0.01 0.00 0.01 10 outer_join_found 43.00 0.00 0.00 0.00 10 outer_join_not_found 30.00 0.01 0.01 0.02 500 outer_join_on_key 33.00 0.01 0.00 0.01 10 select_1_row 3.00 0.69 0.33 1.02 10000 select_2_rows 3.00 0.76 0.34 1.10 10000 select_big 80.00 62.30 7.22 69.52 80 select_big_str 43.00 21.94 4.30 26.24 10000 select_column+column 3.00 0.78 0.28 1.06 10000 select_diff_key 134.00 0.43 0.03 0.46 500 select_distinct 12.00 4.33 0.42 4.75 800 select_group 116.00 3.43 0.31 3.74 2911 select_group_when_MANY_tables 33.00 1.50 0.39 1.89 10000 select_join 2.00 1.29 0.14 1.43 100 select_key 199.00 114.40 9.78 124.18 200000 select_key2 206.00 130.39 7.70 138.09 200000 select_key2_return_key 202.00 105.77 8.57 114.34 200000 select_key2_return_prim 206.00 104.26 10.30 114.56 200000 select_key_prefix 450.00 122.70 9.44 132.14 200000 select_key_prefix_join 18.00 11.52 1.24 12.76 100 select_key_return_key 193.00 111.64 8.51 120.15 200000 select_many_fields 24.00 9.60 5.01 14.61 2000 select_query_cache 89.00 7.25 0.51 7.76 10000 select_query_cache2 89.00 7.20 0.58 7.78 10000 select_range 183.00 23.29 2.51 25.80 410 select_range_key2 26.00 12.69 1.00 13.69 25010 select_range_prefix 26.00 12.60 1.07 13.67 25010 select_simple 2.00 0.57 0.26 0.83 10000 select_simple_join 3.00 1.63 0.15 1.78 500 update_big 35.00 0.00 0.00 0.00 10 update_of_key 82.00 4.42 1.28 5.70 50000 update_of_key_big 25.00 0.07 0.02 0.09 501 update_of_primary_key_many_keys 180.00 0.04 0.01 0.05 256 update_with_key 117.00 20.34 6.68 27.02 300000 update_with_key_prefix 24.00 5.68 2.18 7.86 100000 wisc_benchmark 6.00 4.33 0.44 4.77 114 TOTALS 7074.00 1520.16 164.45 1684.61 2667247 [root@localhost sql-bench]# Here at: Sunday 17th April 2005 12:49, AHD