======================
Step 1.Check the Installation
=> service mysql status
=> pgrep apache2
=> netstat -tap | grep mysql
Step 2. Remove Previous Installation (if needed)
(Be sure! this will delete everything associated with MySQL on ubuntu! It does give you a fresh install!)
2.a. Using Advanced Package Tool (apt)
=> apt-get --purge remove mysql-server mysql-common mysql-client
libmysqlclient15-dev libmysql-ruby
=> apt-get autoremove
=> apt-get autoclean
Or Using Aptitude
=> aptitude remove mysql-server mysql-common mysql-client
libmysqlclient15-dev libmysql-ruby
2.b. Check anything depends on the installed packages
=> apt-cache rdepends mysql-server
=> apt-cache rdepends mysql-client
2.c. Change apparmor setting if needed (if u have changed)
=> pico /etc/apparmor.d/usr.sbin.mysqld
2.d. Delete mysql directory (to delete preferences)
=> rm -rf /etc/mysql
2.e. Find all the files with ‘msyql’ and delete them
=> find / -iname ‘mysql*’ -exec rm -rf {} \;
Step 3: Install MySQL
=> apt-get install mysql-server mysql-common mysql-client libmysqlclient15-dev
libmysql-ruby phpmyadmin
Step 4: Check Install MySQL (where it is running or not)
=> service mysql status
(it will show like : mysql start/running, process 1055)
=> pgrep apache2
=> netstat -tap | grep mysql
Check with mysqladmin
=> mysqladmin -u root -p status
Step 5: Set mysql bind address
(this changes is required to access database from other computer in a network but it may causes security problem. No need to change if the applications which require mysql are running on the same PC)
5.a. For Static IP Address
=> nano /etc/mysql/my.cnf
Change the line:
bind-address = localhost
to your own internal ip address e.g. 192.168.1.20
bind-address = 192.168.1.20
5.b. For Dynamic IP Address
Comment out the bind-address line, it will default to your current IP.
(Can not connect to mysql error 10061" message is shown if anybody try to connect without changing the bind-address.
Step 6: Set mysql root password
6.a. Before accessing the database by console you need to type:
=> mysql -u root (if already have not set a mysql password)
=> mysql -u root -p (if already mysql-root passwor have set)
6.b. At the mysql console type:
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('yourpassword');
e.g: mysql> set password for 'root'@'localhost' = password('123456');
A successful mysql command will show:
Query OK, 0 rows affected (0.00 sec)
N.B: Mysql commands can span several lines but Mysql command must have to end with semicolon
6.c. Alternate way
It is easy to set the mysql root password and create a database using mysqladmin
=> mysqladmin -u root -p password yourpassword and
=> mysqladmin -u root -p create database1
e.g:
root@ubuntu10:~# mysqladmin -u root -p password 123456
Enter password:
root@ubuntu10:~# mysqladmin -u root -p create userDb
Enter password:
Step 7: Create a mysql database
(mysql is not case sensitive except some special cases)
7.a.
=> mysql -u root -p
mysql> CREATE DATABASE myDatabase;
7.b. Create a mysql user
7.b.(i) Create user with all privileges, needed for troubleshooter
mysql> GRANT ALL PRIVILEGES ON *.* TO 'yourusername'@'localhost' IDENTIFIED BY 'yourpassword' WITH GRANT OPTION;
7.b.(ii) Create user for specific database (e.g: myDatabase) with fewer privileges
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON myDatabase.* TO 'yourusername'@'localhost' IDENTIFIED BY 'yourpassword';
Here, myDatabase is the name of the database the user gets access to, localhost is the location which gets access to the database. It can be changed to '%' (or to hostnames or ip addresses) to allow connections from every location (or only from specific locations) to the database.
7.b.(iii) To exit the mysql prompt type:
mysql> \q
Since the mysql root password is now set, if we need to use mysql again (as the mysql root), type the following command
=> mysql -u root -p (then enter the password at the prompt)
Step 7: PhpMyAdmin
All mysql tasks including setting the root password and creating databases can be done via a graphical interface using phpmyadmin or mysql-admin.
7.a. Install PhpMyAdmin
=> sudo apt-get install phpmyadmin
7.b. Test PhpMyAdmin
Open browser and type : http://localhost/phpmyadmin to start it
You should be able to login using any users you've setup in MySQL. If no users have been setup, use admin with no password to login.
If you get a 404 "Not Found" error when you point your browser to the location of phpMyAdmin (such as: http://localhost/phpmyadmin), this is likely caused by not checking the 'Apache 2' selection during installation.
7.c. To Redo the Installation
=> dpkg-reconfigure -plow phpmyadmin
Then select Apache 2 for the webserver you wish to configure.
If this does not work, then you can do the following to include the phpMyadmin-shipped Apache configuration into Apache:
=> ln -s /etc/phpmyadmin/apache.conf /etc/apache2/conf.d/phpmyadmin.conf
=> /etc/init.d/apache2 reload
Step 8: Copy Previous Database manually
8.a. Stop MySQL
=> /etc/init.d/mysql stop
8.a. Copy the database
=> cp -Rp /mountpoint/var/lib/mysql /var/lib/mysql
where "mountpoint" is the path to the mount (address of the databses). e.g:
=> cp /home/mukul/Desktop/localhost.sql /var/lib/mysql/
Now restart MySQL with "sudo /etc/init.d/mysql start". With any luck, MySQL should restart, and you should have your old databases back.
("R" and "p" options are used with the cp commands in order to copy the entire directory contents and retain the existing owner, group, and permissions.)
Or
8.b. Import your previous database using phpmyadmin
8.b.(i). type http://localhost/phpmyadmin/ in your browser
8.b.(ii). type user name and password (e.g: user: root password: 123)
8.b.(iii). Click on Import then browse your database file and click
on Go .
U will see "Import has been successfully finished, -- queries executed." message
To upload file may it is required to alter the max_upload value in php.ini.
To change that file,
=> gedit /etc/php5/apache2/php.ini
then find the line that says something like max_upload and change it to a value that corresponds to the maximum size of the .sql file.
8.b.(iv). Restart apache server
=> /etc/init.d/apache2 force-reload
Step 9: Troubleshooting PhpMyAdmin
If you get blowfish_secret error: Choose and set a phrase for cryptography in the file /etc/phpmyadmin/blowfish_secret.inc.php and copy the line (not the php tags) into the file /etc/phpmyadmin/config.inc.php or you will receive an error.
If you get a 404 error upon visiting http://localhost/phpmyadmin: You will
need to configure apache2.conf to work with Phpmyadmin.
=> gedit /etc/apache2/apache2.conf
Include the following line at the bottom of the file
Include /etc/phpmyadmin/apache.conf (then save and quit)
Step 10: Troubleshooting mysql-admin
10.a.Check mysql status
=> root@ubuntu10:~# chkconfig mysql
10.b. How to Reset MySql Password (for ERROR: 1045)
First Stop the Mysql Service
=>service mysql stop
Start the mysqld demon process using the --skip-grant-tables option with this command
=> /usr/sbin/mysqld --skip-grant-tables --skip-networking &
Because you are not checking user privileges at this point, it's safest to disable networking. In Dapper, /usr/bin/mysgld... did not work. However, mysqld --skip-grant-tables did.
Start the mysql client process using this command
=> mysql -u root
From the mysql prompt execute this command to be able to change any password
=> FLUSH PRIVILEGES;
Then reset/update your password
=> SET PASSWORD FOR root@'localhost' = PASSWORD('password');
If you have a mysql root account that can connect from everywhere, you should also do:
=> UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root';
10.c. Alternate Method
=> USE mysql
=> UPDATE user SET Password = PASSWORD('newpwd') WHERE Host = 'localhost' AND User = 'root';
And if you have a root account that can access from everywhere:
=> USE mysql
=> UPDATE user SET Password = PASSWORD('newpwd') WHERE Host = '%' AND User = 'root';
For either method, once have received a message indicating a successful query (one or more rows affected), flush privileges:
=> FLUSH PRIVILEGES;
Then stop the mysqld process and relaunch it with the classical way:
=> /etc/init.d/mysql stop
=> /etc/init.d/mysql start
10.d. To forcefully stop a process
e.g: stop mysql forcefully:
=> root@ubuntu10:~# pgrep mysqld
2576
=> root@ubuntu10:~# kill 2576
10.e. See mysql log file
=> root@ubuntu10:~# cat /var/log/mysql.log
=> root@ubuntu10:~# cat /var/log/mysql.err
Backup-Settings
Very soon will come...
(taken help from net)
No comments:
Post a Comment