Dump a database:
mysqldump database > path to backup_filename.sql
restore a database
mysql database_name < path to backup_filename.sql
mysql kutschur_genealogy < kutschur_genealogy.sql
Restore this database with the backup_filename.sql:
mysql -u dbuser -p database < backup_filename.sql
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
we can copy a table from the backup to the current backup. For that we have to copy 3 files for each table.
.MYI
.frm
.MYD
We cannot copy these 3 files from a .sql format because .sql is a file. So create a test database and restore the .sql and from that test database we can copy the 3 files.
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
we can also repair each table with the following command.
mysql> repair table tablename;
Or through the phpMyAdmin
============================
create database
mysql>create database dbname;
create user
grant CREATE,INSERT,DELETE,UPDATE,SELECT on dbname.* to user2@localhost;
give password to that user
set password for user2 = password('passwd');
root@host [/var/lib/mysql]# mv kutschur_genealogy kutschur_genealogy.bak
mysql>create database flyerweb_test;
root@host [/backup/cpbackup/daily/kutschur/mysql]# mysql kutschur_genealogy < kutschur_genealogy.sql
===========================
enter that database
myisamchk *.MYI
myisamchk -ref *.MYI
=======================
Yes, you could connect to the database from another server. For this privileges should be added to the user trying to connect from another ip. So please let us know the ip of the server from which you are trying to connect so that we could add the necessary privileges.
Also please note that you could use the domain name as the hostname.
=============================
/scripts/grabmysqlprivs
mysql_fix_privilege_tables
==============================
give privilege
mysql>grant all privileges on dbname.* to user@localhost identified by 'password';
grant all privileges on bershh_oscmax2.* to bershh_oscmax2@localhost identified by 'password';
==========================
entering mysql
#mysql -u mysqlusername -ppassword
or
mysql -u mysqlusername -p
=========================
/tmp consist of mysql ling file. if not following command is used
/tmp#ln -s /var/lib/mysql/mysql.sock .
or
/tmp#ln -s /var/lib/mysql/mysql.sock mysql.sock
===============================
scripts for mysqldata bases permission
/scripts/cleanupmysqlprivs
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
to check the mysql process
# mysqladmin -i2 processlist
# mysqladmin processlist
it will show which database is using more and which user. So move db to db.bkp and after some time move back
&&&&&&&&&&&&&&&&&&&&&&&&&
mysql commands
>show databases;
>use databasename;
>show tables;
>describe tablename; or >desc tablename;
> select field from tablename;
>select * from tablename;
>desc tablename;
&&&&&&&&&&&&&&&&&&&&
restore database
cd /backup/cpbackup/daily/username/mysql
before this take the backup of existing database in /var/lib/mysql to user_mysql.bak from user_mysql
#mysql user_dbname < /backup/cpbackup/daily/username/mysql/user_dbname.sql
&&&&&&&&&&&&&&&&&
repair database
#mysqlcheck databasename
&&&&&&&&&&&&&&&&&&&&&&&
Dump a database:
mysqldump -c -a -v --add-drop-table -u dbuser -p database > backup_filename.sql
Restore this database with the backup_filename.sql:
mysql -u dbuser -p database < backup_filename.sql
&&&&&&&&&&&&&&&&&&&&&&&&
error 1064
http://forums.cpanel.net/showthread.php?t=47284&highlight=%231064+have+an+error+in+your+SQL+syntax
solution
http://dev.mysql.com/doc/refman/4.1/en/downgrading-to-4-0.html
replacing latin1 from sql file
:%s/ DEFAULT CHARSET=latin1//g
again error
ERROR 1064 at line 2345: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'collate latin1_bin NOT NULL default '',
`word_id` mediumint(8
shell> mysqldump --create-options --compatible=mysql40 db_name > dump_file
solution
remove the word collate latin1_bin from sql file
http://forums.cpanel.net/showthread.php?p=277183#post277183
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
how to change database password using phpmyadmin
http://www.tamba2.org.uk/wordpress/phpmyadmin/
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
mysql> create database alphystest;
Query OK, 1 row affected (0.01 sec)
Step2. Create user alphy and give the full permissions on the database named alphystest
Query OK, 0 rows affected (0.00 sec)
Step3: Set up the password for the user .
mysql> set password for user = password('');
Query OK, 0 rows affected (0.02 sec)\
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
error
Got error 28 from table handler
solution
df -h
disk space full
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
for checking error of mysql through shell while it is not starting
binary of mysql --defaults-file=/etc/my.cnf --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-locking
to find binary of mysql
# which mysql
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
restore a table from backup
first of all create a test database. then restore the database from backup. then use the following command
RESTORE TABLE wp_posts FROM '/var/lib/mysql/asefalkm_busungar1';
this command is used to restore a table from a databse
first restore the database from asefalkm_busungar.sql to asefalkm_busungar1 database
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
mysql wont start. it is giving an error related with root privilege.
exact error is given below.
DBI connect('mysql:localhost','root',...) failed: Access denied for user: 'root@localhost' (Using password: YES) at /usr/local/cpanel/Cpanel/Mysql.pm line 42 ERROR 1045: Access denied for user: 'root@localhost' (Using password: YES)
solution
step 1
---------
add the following line at the top section of my.cnf file
skip-grant-tables
step 2
------
Enter mysql prompt
mysql> FLUSH PRIVILEGES;
then grant the root privilege
or
http://forums.cpanel.net/showthread.php?t=28012&highlight=skip-grant-tables
1. Edit the file /root/.my.cnf
2. Use the mysql root password in the file and reset the mysql password to that password in .my.cnf file.
Code:
/etc/init.d/mysqld stop safe_mysqld --skip-grant-tables mysql USE mysql UPDATE user SET password=password("Password in .my.cnf file") WHERE user="root"; flush privileges; exit; killall mysqld
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
pear module
error
Warning: connect(DB/mysql.php) [function.connect]: failed to open stream: No such file or directory in /usr/local/lib/php/DB.php on line 371
connect: not found
solution
after php compilation pear module is installed. But here it needs a module DB inside the pear module. For installling this.
# pear install DB (this command will install this)
for checking the pear module
# pear list
or
which safe_mysqld
/usr/bin/safe_mysqld
root@swordfish [/backup/cpbackup/daily/files]# /usr/bin/safe_mysqld --skip-grant-tables --user=root start &
mysql > UPDATE mysql.user SET Password=PASSWORD('newpwd')
-> WHERE User='root';
mysql> FLUSH PRIVILEGES;
**********************************************************************************************************************************
Mysql repair can give lack of tmp space error like the one given below when you attempt to repair a large database (Gb range). This happens when the /tmp disk space is not enough.
Error:
myisamchk: Disk is full writing '/tmp/ST0HOcXR' (Errcode: 28). Waiting for someone to free space... Retry in 60 secs
You can correct this by changing the value of the variable tmpdir. If the table is repaired through cpanel, WHM, or in the mysql prompt, this can be corrected by temporarily starting mysql with a different tmpdir.
Find a larger partition using df -h. Usually /home is pretty big.
Create new directory called mysqltemp at /home/mysqltemp and chmod it 0777
Edit /etc/my.cnf under [mysqld] group add "tmpdir = /home/mysqltemp"
Restart mysql server and you're new tmpdir for mysql will be at /home/mysqltemp
This way you can repair the DB successfully and then put back mysql as before with default tmpdir.
If you are repairing using the Myisamchk command to repair, then run the repair command with tmpdir specified in it:
myisamchk -r --tmpdir=/home/mysqltmp table_name.MYI
Ref: http://dev.mysql.com/doc/connector/j/en/temporary-files.html
From man myisamchk : -t, --tmpdir=path Path for temporary files
***************************************************************************************************************************