I zipped up a database on one server and copied it to another(both Unix). Then i unzipped the files and placed them in the correct database. Now when I go to the MySQL DB on the second server and write a select statement it works, but I am not able to do an insert/update/delete. I see this error:
ERROR 1036 (HY000): Table is read only
I tried running chmod 755 on all the files in the database, without success. Any suggestions? Thanks in advance.
Try restarting mysql.
Thanks for the advice januzi. How do i restart MySQL? DO I need to restart the Unix server or can I just restart MySQL from the command line. Please excuse my ignorance, as I am new to Unix environment.
Apart from:
chmod 755
Have you checked that the files are owned by mysql?
Otherwise you should run:
chown -R mysql /your/mysql/datadir
Thanks for all the help guys, I tried chmod 777 and I tried chown but it doesnt work. I am not sure if I can restart MySQL, as this is a production database, and I do not want to break anything. Any other ideas?
So you copied the database without stopping the MySQL server?
Then the bets are pretty much off.
The only thing I can think of then is the:
FLUSH TABLES;
Which will force a close on all open tables, but at that point a restart on the mysql server is just as good.
May I ask why you are playing around with copying tables like this into a production server?
Are they very huge or is there some other reason why you don’t use a SQL dump of the table and recreate it on the production server?
The database exists on the server, I copied a few tables over from another server(using tar) and placed them in the database folder. I did this without stopping the MySQL server.
It’s a small company and our Unix admin left so I have to take care of this stuff for a while. They are actually very small tables less than a 1MB
So could you tell me what the impact of restarting MySQL would be? And is there any workaround?
And yes, I tried mysqldump but I keep getting an error that says
mysqldump:command not found.
I am running this from the command line and not from inside MySQL and I am running it as a superuser. I also tried running it after navigating to the directory where I have mysql and also from mysql/bin where I can see mysqldump
Is there phpmyadmin ? As root create new database, create user with full rights to that database. Log in phpmyadmin as that user and import dumped tables.
I do not have phpmyadmin on my machine. I am not sure if its present on the server. Could you be more specific, or point me to link where this is explained clearly. Thank you. Also I need to contact my IT dept to restart MySQL, which would take a week or so.
http://www.phpmyadmin.net/home_page/index.php
It needs http server with php+mysql(i)
As for console, You could:
mysql -u root -p
create database
create user for that database
exit from mysql console
mysql -u root -p dbname < dbname.sql
I do not have a sql dump file with a .sql extension. I zipped the files inside the DB on one server and copied them to another and placed them inside a DB folder of the same name as on the first server.
So I already have a DB and a user for it, I granted all rights to the user too. I am able to select but not able to insert/update/delete onthe tables I copied over from the other server. I am able to insert/update/delete from the tables I created freshly in the database.
| [B]thornton wrote on Wed, 22 April 2009 21:26[/B] |
| And yes, I tried mysqldump but I keep getting an error that says mysqldump:command not found. I am running this from the command line and not from inside MySQL and I am running it as a superuser. I also tried running it after navigating to the directory where I have mysql and also from mysql/bin where I can see mysqldump |
./mysqldump -uroot -p yourDatabase yourTableName > yourDumpFile.sql
The important part is the ./ at the beginning.
Because in contrast to Windows the current directory is not part of the PATH in Unix.
Then on your new server:
./mysql -uroot -p yourDatabase < yourDumpFile.sql
And you should be able to do this without restarting mysql.
Hopefully the DROP TABLE yourTable; command will still work, even though you can’t insert/update to the table.
If this doesn’t work you will have to restart the server.
The impact will most probably only be that the database is not available during the few moments it takes to restart it.
| [B]Quote:[/B] |
|
So could you tell me what the impact of restarting MySQL would be? And is there any workaround? |
It didn’t solve my problem completely, but thats awesome, i didn’t find that tip anywhere. I tried the mysqldump with ./ and it worked. now I am able to take a sqldump of my DB. but when i got to the second server and try to restore it using
./mysql -uroot -p yourDatabase < yourDumpFile.sql
it prints out a lot of data from the tables, but it doesnt actually cipy all the tables from the dump. I can still only see the existing tables and not the new ones. Any ideas?
By using the:
mysql -uroot -p yourDatabase
frontend GUI command.
Did you try the first:
DROP TABLE yourTable;
in your dumpfile?
And did you get an error or was it successful?
If you got any other error than “Unknown table” it means that you can’t do any thing to the table and you will have to try either:
FLUSH TABLES;
Or restart the mysql process.
If it was successful then try and cut and paste the CREATE TABLE statement from the sql file and see if you can create the table.
The sql file is a text file and you can try each command by cut and paste it into the mysql interactive GUI.
Whew. Got it. I forgot that the .sql file was a text file(and I use SQL server 2005 everyday). Anyways, dropped my existing table, and opened the .sql file in SQL server and copied the create table code and created a table. Then I copied all the insert statements and ran them. Now I have the table copied over and insert works too. Thanks a lot sterin71 and januzi. You guys are awesome.
MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright © 2006 - 2021 Percona LLC. All rights reserved.