Mysql – How should I reinit a new db (windows 8 64b)

MySQL

In the data directory of Mysql some files are corrupt. Recovering is not working anymore. I have a complete sql backup and want to setup a new db. The complete stuff is installed with the use of wampserver.

I just renamed the :
D:\wamp\bin\mysql\mysql5.6.17\data
to
D:\wamp\bin\mysql\mysql5.6.17\data_old

and made a new folder called
D:\wamp\bin\mysql\mysql5.6.17\data

Changed socket 3306 to 3307 but now getting:

Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist

How should I reinit a new db (windows 8 64b)?

Best Answer

you would require mysql folder from the data_old directory. As it looks for mysql related tables whenever you initiate mysql on with some other folder. Perform following steps in sequence

1 stop MySQL instance for old instance

2 copy data folder or system databases folders(mysql,performace_schema,information_schema,test) folder from "D:\wamp\bin\mysql\mysql5.6.17\data_old" to the new path " D:\wamp\bin\mysql\mysql5.6.17\data"

3 mysqld --install --defaults-file =Absolute Path to .ini file

If you are successful, the message “Service successfully installed.” will appear

If so you should be able to connect to new MySQL instance with new port.

Hope it helps

Related Solutions

MySQL InnoDB logfiles resized – review thesql-err.log for potential problems

It is sort of yes and no. Why would I say both?

There are still some transactional data embedded in the old ib_logfiles in conjunction with the ibdata1 file. (See pictorial representation)

What you should have done is this:

mysql -u... -p... -ANe"SET GLOBAL innodb_fast_shutdown = 0"
/etc/init.d/mysql.server stop
mv ib_logfile0 ib_logfile0.OLD
mv ib_logfile0 ib_logfile0.OLD
/etc/init.d/mysql.server start

If you did not know of disabling innodb_fast_shutdown, ther you steps should have been

/etc/init.d/mysql.server stop
/etc/init.d/mysql.server start
/etc/init.d/mysql.server stop
mv ib_logfile0 ib_logfile0.OLD
mv ib_logfile0 ib_logfile0.OLD
/etc/init.d/mysql.server start

Doing either of these coordinate the purging of all uncommitted writes to tables and indexes. Just looking at the error log, I think you dodged a bullet in this instance. In future, please follow this protocol.

If you are not sure either way, you could put back the old log files, set the old size of innodb_log_file_size, restart mysql. Then, start the process over again as I prescribed above.

Thesql 5.1 — unknown variable ‘basedir=c:/thesql/’

Thank for posting the my.ini

PROBLEM: You have the basedir and datadir under the [client] section

basedir and datadir are server-only options, not client program options.

SOLUTION : Remove them from under the [client] section and you are good to go !!!

When done, this

[client]
port= new_port

##The MySQL server
Path to installation directory. All paths are usually resolved relative to this.
basedir="C:/MySQL/"
#Path to the database root
datadir="C:/MySQL/"
#Set the default character set.
default-character-set=latin1

should now be this:

[client]
port= new_port

Best Answer

Related Solutions

MySQL InnoDB logfiles resized – review thesql-err.log for potential problems

Thesql 5.1 — unknown variable ‘basedir=c:/thesql/’

Give it a Try !!!

Related Question