Hazardinho

[Help] Sqlite and Mysql

Recommended Posts

Hello, I need a little help, I'm creating a server where your database is through SQLite, and I, to reinforce data security, I wanted to make a backup on MySql, copying the table to MySql in case there is a problem may be recovering, can someone help me ?, is it possible?

Share this post


Link to post

Hello Hazardinho,

how about dumping the SQLite database into one big query .sql file and running that query inside of your MySQL server to load the database? Take a look at the following article:

https://www.sqlitetutorial.net/sqlite-dump/

I have no experience with dumping from SQLite to MySQL but I think that the query format should be very compatible. ;) 

Share this post


Link to post
39 minutes ago, The_GTA said:

Olá Hazardinho,

Que tal despejar o banco de dados SQLite em um grande arquivo .sql de consulta e executar essa consulta dentro do servidor MySQL para carregar o banco de dados? Dê uma olhada no seguinte artigo:

https://www.sqlitetutorial.net/sqlite-dump/

Não tenho experiência com o dumping do SQLite para o MySQL, mas acho que o formato da consulta deve ser muito compatível. ;) 

I made a small system where every day at 4 o'clock in the morning, it disconnects all players and saves saved tables, but I find it very rustic and giving some errors, I have mastered the basics of SQL so I wanted to know a method , this method you put there the server itself may be saving?

Share this post


Link to post
6 minutes ago, Hazardinho said:

I made a small system where every day at 4 o'clock in the morning, it disconnects all players and saves saved tables, but I find it very rustic and giving some errors, I have mastered the basics of SQL so I wanted to know a method , this method you put there the server itself may be saving?

Actually no, the system I recommended does not work if you want to dump while the server is operating on the database. If you want to dump during server runtime you absolutely have to do so with a server resource. Maybe like this...

  1. output a server message that the server is saving data, thus not responding for a while
  2. save the entire game state to the database; this is so that the database is in a consistent state
  3. create a .sql file into the resource directory
  4. write the table layouts as CREATE TABLE commands into the .sql file
  5. dump the table contents as INSERT INTO commands into the .sql file
  6. flush and close the .sql file
  7. resume server execution
  8. output a server message that the server has finished saving data

Sorry but I have no easy solution for you. It would be best if MTA itself had a dumping function since this feature seems pretty important for data security.

Share this post


Link to post
23 hours ago, The_GTA said:

Na verdade não, o sistema que eu recomendei não funcionará se você quiser fazer o dump enquanto o servidor estiver operando no banco de dados. Se você deseja fazer o dump durante o tempo de execução do servidor, é absolutamente necessário fazê-lo com um recurso do servidor. Talvez assim ...

  1. gera uma mensagem de servidor informando que o servidor está salvando dados, não respondendo por um tempo
  2. salve o estado inteiro do jogo no banco de dados; isso é para que o banco de dados esteja em um estado consistente
  3. crie um arquivo .sql no diretório de recursos
  4. escreva os layouts de tabela como comandos CREATE TABLE no arquivo .sql
  5. despejar o conteúdo da tabela como comandos INSERT INTO no arquivo .sql
  6. esvazie e feche o arquivo .sql
  7. retomar a execução do servidor
  8. enviar uma mensagem do servidor informando que o servidor terminou de salvar os dados

Desculpe, mas não tenho uma solução fácil para você. Seria melhor se o próprio MTA tivesse uma função de dumping, pois esse recurso parece muito importante para a segurança dos dados.

I can't even imagine how to start it, I don't know anything about sql ... :(

Share this post


Link to post

Which resources are storing SQL data and do you know what kind of data it is? Since you are making a server does it mean that you are creating scripts that use SQL for it too? Your support request might turn into a resources question: do all resources properly store their SQL data at any given point x between events? But if we get a better idea about your server resources setup we could imagine how to start it. 😉

Share this post


Link to post
20 hours ago, The_GTA said:

Quais recursos estão armazenando dados SQL e você sabe que tipo de dados são? Como você está criando um servidor, isso significa que você também está criando scripts que usam SQL? Sua solicitação de suporte pode se transformar em uma pergunta de recursos: todos os recursos armazenam adequadamente seus dados SQL em um determinado ponto x entre eventos? Mas se tivermos uma idéia melhor sobre a configuração dos recursos do servidor, poderemos imaginar como iniciá-lo. 😉

Basically they are login resources etc, yes it does have a resource where the SQL is located and does the saving, the way I tried to do it was to create the tables in MySql, kind of manue and after calling a for with all the data, it gave until right, but it did give some error:

Method:

 

function savedatabase ()
dbHandler = dbConnect("mysql","dbname=".. sqlDatas["database"] ..";host="..sqlDatas["host"], sqlDatas["user"], sqlDatas["pw"], "autoreconnect=1")
     if dbHandler then
         print('O Database foi conectado.')
     else
         error('O Database não foi conectado.')
     end
     deletTabela ()
end

function deletTabela ()
	 dbExec ( dbHandler, "DROP TABLE `Admin`" )
	 dbExec ( dbHandler, "DROP TABLE `Banimentos`" )
	 dbExec ( dbHandler, "DROP TABLE `Caracters`" )
	 dbExec ( dbHandler, "DROP TABLE `Dados`" )
	 dbExec ( dbHandler, "DROP TABLE `Logins`" )
	 dbExec ( dbHandler, "DROP TABLE `Policia`" )
	 dbExec ( dbHandler, "DROP TABLE `items`" )
	 dbExec ( dbHandler, "DROP TABLE `safe`" )
	 print('Tabelas removidas.')
	 _call(createTabelaSql);
end

function createTabelaSql ()
	 dbExec(dbHandler, "CREATE TABLE IF NOT EXISTS Dados ( ID TEXT, Nome TEXT, Data TEXT, Senha TEXT, Email TEXT, Pais TEXT, Estado TEXT, Cidade TEXT, IP TEXT, Serial TEXT)") 
	 dbExec(dbHandler, "CREATE TABLE IF NOT EXISTS Logins ( Serial TEXT, Atualiza TEXT, Online TEXT, Nome TEXT, Admin TEXT, AdminName TEXT, BP TEXT, account TEXT, password TEXT, LastLogin TEXT, Adv TEXT, IRRAccount TEXT, ReasonIRR TEXT)") 
	 dbExec(dbHandler, "CREATE TABLE IF NOT EXISTS Caracters ( Serial TEXT, ID TEXT, Pos TEXT, Money TEXT, MoneyS TEXT, RG TEXT, Sexo TEXT, Hp TEXT, Fome TEXT, Sede TEXT, Cj TEXT, Skin TEXT, Roupa TEXT, Level TEXT, Exp TEXT)") 
	 dbExec(dbHandler, "CREATE TABLE IF NOT EXISTS items ( actionslot TEXT, slot TEXT, itemid TEXT, value TEXT, count TEXT, owner TEXT, type TEXT, dutyitem TEXT)") 
	 dbExec(dbHandler, "CREATE TABLE IF NOT EXISTS safe ( Position TEXT, Interior TEXT, Dimension TEXT)") 
	 dbExec(dbHandler, "CREATE TABLE IF NOT EXISTS Policia ( RG TEXT, Nome TEXT, Passagens TEXT, Artigo TEXT)") 
	 dbExec(dbHandler, "CREATE TABLE IF NOT EXISTS Admin ( ID TEXT, ADML TEXT, STATES TEXT, LL TEXT, CH TEXT, H TEXT, M TEXT, HT TEXT, MN TEXT, RK TEXT)") 
	 dbExec(dbHandler, "CREATE TABLE IF NOT EXISTS Banimentos ( Serial TEXT, Responsavel TEXT, Dias TEXT, Data TEXT)") 
	 print('Tabelas criada.')
	 _call(loadDataLogin);
end

function loadDataLogin ()
local loaderQuery = dbPoll(dbQuery(sql, "SELECT * FROM Logins"), -1)
     if loaderQuery then
	 for i, v in ipairs(loaderQuery) do
		     local serial = tostring(v["Serial"]  or 0)
			 local att    = tonumber(v["Atualiza"]  or 0)
			 local on     = tostring(v["Online"]  or 0)
			 local name   = tostring(v["Nome"]  or 0)
			 local admin  = tonumber(v["Admin"]  or 0)
			 local adinna = tostring(v["AdminName"]  or 0)
			 local bp     = tonumber(v["BP"]  or 0)
			 local accoun = tostring(v["account"]  or 0)
			 local pass   = tostring(v["password"]  or 0)
			 local lastlo = tostring(v["LastLogin"]  or 0)
			 local adv    = tonumber(v["Adv"]  or 0)
			 local irrac  = tonumber(v["IRRAccount"]  or 0)
			 local reason = tostring(v["ReasonIRR"]  or 0)
             dbFree(dbQuery(dbHandler, "INSERT INTO Logins (Serial, Atualiza, Online, Nome, Admin, AdminName, BP, account, password, LastLogin, Adv, IRRAccount, ReasonIRR) VALUES ('"..serial.."', '"..att.."', '"..on.."', '"..name.."', '"..admin.."', '"..adinna.."', '"..bp.."', '"..accoun.."', '"..pass.."', '"..lastlo.."', '"..adv.."', '"..irrac.."', '"..reason.."')"))
			 sleep(500);
		 end
	 end
	 print('Tabela: Logins criada com sucesso.')
	 _call(loadDataAdmin);
end

function loadDataAdmin ()
local loaderQuery = dbPoll(dbQuery(sql, "SELECT * FROM Admin"), -1)
     if loaderQuery then
	 local values = nil
         for i, v in ipairs(loaderQuery) do
		     local id     = tonumber(v["ID"]  or 0)
			 local adml   = tonumber(v["ADML"]  or 0)
			 local states = tostring(v["STATES"]  or 0)
			 local ll     = tostring(v["LL"]  or 0)
			 local ch     = tonumber(v["CH"]  or 0)
			 local h      = tonumber(v["H"] or 0)
			 local m      = tonumber(v["M"] or 0)
			 local ht     = tonumber(v["HT"] or 0)
			 local mn     = tonumber(v["MN"] or 0)
			 local rk     = tonumber(v["RK"] or 0)
             dbFree(dbQuery(dbHandler, "INSERT INTO Admin (ID, ADML, STATES, LL, CH, H, M, HT, MN, RK) VALUES ('"..id.."', '"..adml.."', '"..states.."', '"..ll.."', '"..ch.."', '"..h.."', '"..m.."', '"..ht.."', '"..mn.."', '"..rk.."')"))
			 sleep(500);
		 end
	 end
	 print('Tabela: Admin criada com sucesso.')
	 _call(loadDataBanimentos);
end

function loadDataBanimentos ()
local loaderQuery = dbPoll(dbQuery(sql, "SELECT * FROM Banimentos"), -1)
     if loaderQuery then
	     for i, v in ipairs(loaderQuery) do
		     local serial   = tostring(v["Serial"]  or 0)
			 local respon   = tostring(v["Responsavel"]  or 0)
			 local dias     = tonumber(v["Dias"]  or 0)
			 local data     = tonumber(v["Data"]  or 0)
             dbFree(dbQuery(dbHandler, "INSERT INTO Banimentos (Serial, Responsavel, Dias, Data) VALUES ('"..serial.."', '"..respon.."', '"..dias.."', '"..data.."')"))
			 sleep(500);
		 end
	 end
	 print('Tabela: Banimentos criada com sucesso.')
	 _call(loadDataCaracters);
end

function loadDataCaracters ()
local loaderQuery = dbPoll(dbQuery(sql, "SELECT * FROM Caracters"), -1)
     if loaderQuery then
	     for i, v in ipairs(loaderQuery) do
		     local serial = tostring(v["Serial"]  or 0)
			 local id     = tonumber(v["ID"]  or 0)
			 local pos    = tostring(v["Pos"]  or 0)
			 local money  = tonumber(v["Money"]  or 0)
			 local moneys = tonumber(v["MoneyS"]  or 0)
			 local rg     = tostring(v["RG"]  or 0)
			 local sexo   = tonumber(v["Sexo"]  or 0)
			 local hp     = tonumber(v["Hp"]  or 0)
			 local fome   = tonumber(v["Fome"]  or 0)
			 local sede   = tonumber(v["Sede"]  or 0)
			 local cj     = tostring(v["Cj"]  or 0)
			 local skin   = tonumber(v["Skin"]  or 0)
			 local roupa  = tonumber(v["Roupa"]  or 0)
			 local level  = tonumber(v["Level"]  or 0)
			 local exp    = tonumber(v["Exp"]  or 0)
             dbFree(dbQuery(dbHandler, "INSERT INTO Caracters (Serial, ID, Pos, Money, MoneyS, RG, Sexo, Hp, Fome, Sede, Cj, Skin, Roupa, Level, Exp) VALUES ('"..serial.."', '"..id.."', '"..pos.."', '"..money.."', '"..moneys.."', '"..rg.."', '"..sexo.."', '"..hp.."', '"..fome.."', '"..sede.."', '"..cj.."', '"..skin.."', '"..roupa.."', '"..level.."', '"..exp.."')"))
			 sleep(500);
		 end
	 end
	 print('Tabela: Caracters criada com sucesso.')
     _call(loadDataDados);
end

function loadDataDados ()
local loaderQuery = dbPoll(dbQuery(sql, "SELECT * FROM Dados"), -1)
     if loaderQuery then
	     for i, v in ipairs(loaderQuery) do
		     local id     = tonumber(v["ID"]  or 0)
			 local nome   = tostring(v["Nome"]  or 0)
			 local data   = tostring(v["Data"]  or 0)
			 local senha  = tostring(v["Senha"]  or 0)
			 local email  = tostring(v["Email"]  or 0)
			 local ips     = tostring(v["IP"] or 0)
			 local serial = tostring(v["Serial"]  or 0)
             dbFree(dbQuery(dbHandler, "INSERT INTO Dados (ID, Nome, Data, Senha, Email, IP, Serial) VALUES ('"..id.."', '"..nome.."', '"..data.."', '"..senha.."', '"..email.."', '"..ips.."', '"..serial.."')"))
			 sleep(500);
		 end
	 end
	 print('Tabela: Dados criada com sucesso.')
     _call(loadDataPolicia);
end

function loadDataPolicia ()
local loaderQuery = dbPoll(dbQuery(sql, "SELECT * FROM Policia"), -1)
     if loaderQuery then
	     for i, v in ipairs(loaderQuery) do
		     local rg     = tonumber(v["RG"]  or 0)
			 local nome   = tostring(v["Nome"]  or 0)
			 local passa  = tonumber(v["Passagens"]  or 0)
			 local artigo = tonumber(v["Artigo"]  or 0)
             dbFree(dbQuery(dbHandler, "INSERT INTO Policia (RG, Nome, Passagens, Artigo) VALUES ('"..rg.."', '"..nome.."', '"..passa.."', '"..artigo.."')"))
			 sleep(500);
		 end
	 end
	 print('Tabela: Policia criada com sucesso.')
     _call(loadDataitems);
end

function loadDataitems ()
local loaderQuery = dbPoll(dbQuery(sql, "SELECT * FROM items"), -1)
     if loaderQuery then
	     for i, v in ipairs(loaderQuery) do
		     local slot   = tonumber(v["slot"]  or 0)
			 local itemid = tonumber(v["itemid"]  or 0)
			 local value  = tonumber(v["value"]  or 0)
			 local count  = tonumber(v["count"]  or 0)
			 local owner  = tonumber(v["owner"]  or 0)
			 local type   = tonumber(v["type"]  or 0)
			 local dutyit = tonumber(v["dutyitem"]  or 0)
             dbFree(dbQuery(dbHandler, "INSERT INTO items (slot, itemid, value, count, owner, type, dutyitem) VALUES ('"..slot.."', '"..itemid.."', '"..value.."', '"..count.."', '"..owner.."', '"..type.."', '"..dutyit.."')"))
			 sleep(500);
		 end
	 end
	 print('Tabela: Items criada com sucesso.')
	 _call(loadDataSafe);
end

function loadDataSafe ()
local loaderQuery = dbPoll(dbQuery(sql, "SELECT * FROM safe"), -1)
     if loaderQuery then
	     for i, v in ipairs(loaderQuery) do
		     local pos    = tostring(v["Position"]  or 0)
			 local interi = tonumber(v["Interior"]  or 0)
			 local dimens = tonumber(v["Dimension"]  or 0)
             dbFree(dbQuery(dbHandler, "INSERT INTO safe (Position, Interior, Dimension) VALUES ('"..pos.."', '"..interi.."', '"..dimens.."')"))
			 sleep(500);
		 end
	 end
	 print('Tabela: Safe criada com sucesso.')
	 print('Backup realizado com sucesso.')
end

 

Share this post


Link to post

Why function "deleteTabela" is using? Ever Delete Data when call function saveDatabase. You want update the Data?

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.